Topic
  • 2 replies
  • Latest Post - ‏2014-02-24T06:59:32Z by Hitsch
Hitsch
Hitsch
2 Posts

Pinned topic How to use wildcards in namespaces within XMLQUERY?

‏2014-01-10T09:52:43Z |

Hi all,

I've to read attributes out of XML-Messages that use different namespace (-versions). From other documents and discussions I learned that wildcards can be used in namespaces within e.g. XMLTABLE. But all ways I tried to transform the solution into XMLQUERY do not work.

In the attachement I added the screenshot of the select statement that currently uses two XMLQUERY one for each namespace and returnting the values in different columns. The idea is to have just one XMLQUERY item that is able to find the attribute in all namespaces.

Any help would be highly appreciated.

Thanks and regards, Christian

  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: How to use wildcards in namespaces within XMLQUERY?

    ‏2014-02-08T02:08:28Z  

    Hi Christian,

    sorry for the delay in responding.

    Let's take this piece of SQL/XML code as an example:

     

    select XMLQUERY ('declare default element namespace "...";
                      for $e in $doc/Jv-Ins-Reinsurance/Settlement
                      return $e/CreationDate/text()'
                      passing d.xml_data as "doc")
           AS ...

    To use namespace wildcards, you change this code as shown here:

     

    select XMLQUERY ('for $e in $doc/*:Jv-Ins-Reinsurance/*:Settlement
                      return $e/*:CreationDate/text()'
                      passing d.xml_data as "doc")
           AS ...

     

    Independent from the namespace question, you can further simplify this code and get the same result:

    select XMLQUERY ('$doc/*:Jv-Ins-Reinsurance/*:Settlement/*:CreationDate/text()'
                      passing d.xml_data as "doc")
           AS ...

     

    And in DB2 LUW 9.5 and later you can also write the code like this:

    select XMLQUERY ('$XML_DATA/*:Jv-Ins-Reinsurance/*:Settlement/*:CreationDate/text()')
           AS ...

    In this example, the XPath variable $XML_DATA refers directly to the XML column in the table.

     

    Does this help?

    Thanks,

    - Matthias

     

  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: How to use wildcards in namespaces within XMLQUERY?

    ‏2014-02-08T02:08:28Z  

    Hi Christian,

    sorry for the delay in responding.

    Let's take this piece of SQL/XML code as an example:

     

    select XMLQUERY ('declare default element namespace "...";
                      for $e in $doc/Jv-Ins-Reinsurance/Settlement
                      return $e/CreationDate/text()'
                      passing d.xml_data as "doc")
           AS ...

    To use namespace wildcards, you change this code as shown here:

     

    select XMLQUERY ('for $e in $doc/*:Jv-Ins-Reinsurance/*:Settlement
                      return $e/*:CreationDate/text()'
                      passing d.xml_data as "doc")
           AS ...

     

    Independent from the namespace question, you can further simplify this code and get the same result:

    select XMLQUERY ('$doc/*:Jv-Ins-Reinsurance/*:Settlement/*:CreationDate/text()'
                      passing d.xml_data as "doc")
           AS ...

     

    And in DB2 LUW 9.5 and later you can also write the code like this:

    select XMLQUERY ('$XML_DATA/*:Jv-Ins-Reinsurance/*:Settlement/*:CreationDate/text()')
           AS ...

    In this example, the XPath variable $XML_DATA refers directly to the XML column in the table.

     

    Does this help?

    Thanks,

    - Matthias

     

  • Hitsch
    Hitsch
    2 Posts

    Re: How to use wildcards in namespaces within XMLQUERY?

    ‏2014-02-24T06:59:32Z  

    Hi Christian,

    sorry for the delay in responding.

    Let's take this piece of SQL/XML code as an example:

     

    select XMLQUERY ('declare default element namespace "...";
                      for $e in $doc/Jv-Ins-Reinsurance/Settlement
                      return $e/CreationDate/text()'
                      passing d.xml_data as "doc")
           AS ...

    To use namespace wildcards, you change this code as shown here:

     

    select XMLQUERY ('for $e in $doc/*:Jv-Ins-Reinsurance/*:Settlement
                      return $e/*:CreationDate/text()'
                      passing d.xml_data as "doc")
           AS ...

     

    Independent from the namespace question, you can further simplify this code and get the same result:

    select XMLQUERY ('$doc/*:Jv-Ins-Reinsurance/*:Settlement/*:CreationDate/text()'
                      passing d.xml_data as "doc")
           AS ...

     

    And in DB2 LUW 9.5 and later you can also write the code like this:

    select XMLQUERY ('$XML_DATA/*:Jv-Ins-Reinsurance/*:Settlement/*:CreationDate/text()')
           AS ...

    In this example, the XPath variable $XML_DATA refers directly to the XML column in the table.

     

    Does this help?

    Thanks,

    - Matthias

     

    Hello Mathias

    thanks a lot for your input and your detailed answer.

    It works fine and save's a lot of coding!

    Regards, Christian