Topic
4 replies Latest Post - ‏2013-06-11T20:52:21Z by MatthiasNicola
Sigmazen
Sigmazen
19 Posts
ACCEPTED ANSWER

Pinned topic Attribute exclusion

‏2013-06-09T23:10:01Z |

Hi
I need to pull an entire 200-attribute xml document but without one or two attributes (for privacy reasons, for example)
Given there are so many current attributes, and the number will increase over time because of the flexibility of xml, I don't want to xmlquery or xmltable each attribute.
So, in the following example I need to omit <person_name> but keeping the rest of the document intact.
    WITH MY_TABLE (ID, MY_XML) AS (
  SELECT 1, XMLPARSE(DOCUMENT('
    <customer>
      <person_name>JOHN SMITH</person_name>
      <address><state>NY</state><country>US</country></address>
      <ssn>1234</ssn>
      <misc>blah blah blah</misc>
 </customer>')) FROM SYSIBM.SYSDUMMY1 UNION ALL
  SELECT 2, XMLPARSE(DOCUMENT('
    <customer>
      <person_name>JACK BROWN</person_name>
      <address><state>NC</state><country>US</country></address>
      <ssn>9876</ssn>
      <misc>blah blah blah</misc>
    </customer>')) FROM SYSIBM.SYSDUMMY1
  )
  SELECT ID, XML2CLOB(A.MY_XML)
    FROM MY_TABLE AS A

Any ideas on how to omit tag/values?
Cheers
Simon

Updated on 2013-06-10T19:31:06Z at 2013-06-10T19:31:06Z by Sigmazen
  • Sigmazen
    Sigmazen
    19 Posts
    ACCEPTED ANSWER

    Re: Attribute exclusion

    ‏2013-06-10T17:39:12Z  in response to Sigmazen

    Hi

    The following working nicely for person_name but not yet for <country> within the <address> parent:

    SELECT XMLSERIALIZE(XMLQUERY('$MY_XML/customer/*[not(name()="person_name") and not(./address/name()="country") ]') AS CLOB)

    Is this possible? Or do I need to omit the whole address and rebuild?

    After this piece I'm wanting to convert the ssn to XXXvalue  so that 1234 becomes XXX4, so I'm thinking I would use something like:

    ('$MY_XML/customer/concat("XXX", substring(ssn,4,1))')

    Finally, once this is done, I should be able to wrap the xmlserialize with the following:

    SELECT XMLPARSE(DOCUMENT('<customer>' || XMLSERIALIZE( . . . ) || '</customer>'))

    and I have my xml document, thus:

    SELECT XMLPARSE(DOCUMENT('<customer>'
    || XMLSERIALIZE(XMLQUERY('$MY_XML/customer/*[not(name()="person_name") and not(name()="address") and not(name()="ssn")]') AS VARCHAR(100))
    || '<ssn>' || XMLCAST(XMLQUERY('$MY_XML/customer/concat("XXX",substring(ssn,4,1))') AS VARCHAR(10)) || '</ssn>'
    || '<address>' || XMLSERIALIZE(XMLQUERY('$MY_XML/customer/address/*[not(name()="country")]') AS VARCHAR(30)) || '</address>'
    || '</customer>'))
    FROM MY_TABLE ASA

    Is this the best approach?

    Cheers

    Simon

    Updated on 2013-06-10T23:50:13Z at 2013-06-10T23:50:13Z by Sigmazen
  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: Attribute exclusion

    ‏2013-06-11T01:56:12Z  in response to Sigmazen

    Hi Simon,

    a different (and possibly simpler) approach would be to use an XML update expression to remove elements or attributes, or modify their values, as needed in the SELECT clause of your query.

    Here are some examples based on the sample table in this article:

    http://www.ibm.com/developerworks/data/library/techarticle/dm-0710nicola/

     

    select xmlquery('copy $new := $INFO
       modify do replace value of $new/customerinfo/phone with "905-xxx-xxxx"
       return $new ')
    from xmlcustomerwhere cid = 1000;

     

    select xmlquery('copy $new := $INFO
       modify do delete $new/customerinfo/phone
       return $new ')
    from xmlcustomerwhere cid = 1000;


    You can also combine multiple changes in a single modify expression:


    select xmlquery('copy $new := $INFO
        modify (  do delete $new/customerinfo/phone ,             
                  do replace value of $new/addr/@country with "none"       )   
        return $new ')
    from xmlcustomerwhere cid = 1000;


    Would that help you?

     

    Matthias

    Updated on 2013-06-11T02:03:45Z at 2013-06-11T02:03:45Z by MatthiasNicola
    • Sigmazen
      Sigmazen
      19 Posts
      ACCEPTED ANSWER

      Re: Attribute exclusion

      ‏2013-06-11T16:35:44Z  in response to MatthiasNicola

      :-)

      Thanks for the speedy reply ... and if I had read a few chapters more, it's all in chapter 12.10 in your excellent book:

      http://www.amazon.com/DB2-pureXML-Cookbook-Master-Hybrid/dp/0138150478/

      So, taking my original CTE, here's the resultant query:

      WITH MY_TABLE (ID, MY_XML) AS (
      SELECT 1, XMLPARSE(DOCUMENT('
      <customer>
      <person_name>JOHN SMITH</person_name>
      <address><state>NY</state><country>US</country></address>
      <ssn>123456789</ssn>
      <misc>blah blah blah</misc>
      </customer>')) FROM SYSIBM.SYSDUMMY1)


      SELECT XMLSERIALIZE(XMLQUERY('copy $new := $MY_XML
      modify ( do delete $new/customer/person_name
      , do delete $new/customer/address/country
      , do replace value of $new/customer/ssn with concat("XXX", substring($new/customer/ssn,4,5))
      )
      return $new ') AS VARCHAR(1000))
      FROM MY_TABLE;

       Perfect :-)

      Cheers

      Simon

  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: Attribute exclusion

    ‏2013-06-11T20:52:21Z  in response to Sigmazen

    Glad this is working for you!

     Matthias