Topic
  • 4 replies
  • Latest Post - ‏2013-03-01T04:48:44Z by NickLawrence
tcj2001
tcj2001
7 Posts

Pinned topic Parse XML with namespace using SQL

‏2013-03-01T00:00:41Z |
How to parse XML that has namespace into table columns using the below SQL
select a.*
FROM
XMLTABLE(
xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS "soap", 'http://chkenergy.iseries.com/' as xmlns),
'$doc/soap:Envelope/soap:Body'
PASSING
xmlparse(document
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><ArtesiaGetVendorResponse xmlns="http://chkenergy.iseries.com/"><ArtesiaGetVendorResult>RAYMOND H ELROD</ArtesiaGetVendorResult></ArtesiaGetVendorResponse></soap:Body></soap:Envelope>'
)
as "doc"
columns
Vendor varchar(50) path 'ArtesiaGetVendorResult'
) as a;

I am able to parse XML that has no namespace with out any problem
SELECT a.*
FROM
XMLTABLE (
'$d/dept/employee'
PASSING
XMLPARSE(document
'<dept bldg="101">
<employee id="901">
<name>
<first>John</first>
<last>Doe</last>
</name>
<office>344</office>
<salary currency="USD">55000</salary>
</employee>
<employee id="902">
<name>
<first>Peter</first>
<last>Pan</last>
</name>
<office>216</office>
<phone>905-416-5004</phone>
</employee>
</dept>'
)
as "d"
COLUMNS
empID INTEGER PATH '@id',
firstname VARCHAR(20) PATH 'name/first',
lastname VARCHAR(25) PATH 'name/last'
) as a ;
Updated on 2013-03-01T04:48:44Z at 2013-03-01T04:48:44Z by NickLawrence
  • NickLawrence
    NickLawrence
    18 Posts

    Re: Parse XML with namespace using SQL

    ‏2013-03-01T00:20:18Z  
    There are two issues with your query.

    1) The column Vendor has a path expression for ArtesiaGetVendorResult (not in any namespace) but the document has this element declared in a different namespace (see the default element namespace defined on the parent element named ArtesiaGetVendorResponse)

    2) You're missing a step/level in your path expression.

    I coded the query this way and I get the correct results. You need to use the DEFAULT keyword to define the default namespace. The other option is to define a prefix and use the prefix to qualify the element. (DEFAULT should be in bold, if the formatting on this post works right)
    select a.*
    FROM
    XMLTABLE(
    xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS "soap", DEFAULT 'http://chkenergy.iseries.com/' ),
    '$doc/soap:Envelope/soap:Body'
    PASSING
    xmlparse(document
    '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><ArtesiaGetVendorResponse xmlns="http://chkenergy.iseries.com/"><ArtesiaGetVendorResult>RAYMOND H ELROD</ArtesiaGetVendorResult></ArtesiaGetVendorResponse></soap:Body></soap:Envelope>'
    )
    as "doc"
    columns
    Vendor varchar(50) path 'ArtesiaGetVendorResponse/ArtesiaGetVendorResult'
    ) as a;
  • NickLawrence
    NickLawrence
    18 Posts

    Re: Parse XML with namespace using SQL

    ‏2013-03-01T01:08:02Z  
    There are two issues with your query.

    1) The column Vendor has a path expression for ArtesiaGetVendorResult (not in any namespace) but the document has this element declared in a different namespace (see the default element namespace defined on the parent element named ArtesiaGetVendorResponse)

    2) You're missing a step/level in your path expression.

    I coded the query this way and I get the correct results. You need to use the DEFAULT keyword to define the default namespace. The other option is to define a prefix and use the prefix to qualify the element. (DEFAULT should be in bold, if the formatting on this post works right)
    select a.*
    FROM
    XMLTABLE(
    xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS "soap", DEFAULT 'http://chkenergy.iseries.com/' ),
    '$doc/soap:Envelope/soap:Body'
    PASSING
    xmlparse(document
    '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><ArtesiaGetVendorResponse xmlns="http://chkenergy.iseries.com/"><ArtesiaGetVendorResult>RAYMOND H ELROD</ArtesiaGetVendorResult></ArtesiaGetVendorResponse></soap:Body></soap:Envelope>'
    )
    as "doc"
    columns
    Vendor varchar(50) path 'ArtesiaGetVendorResponse/ArtesiaGetVendorResult'
    ) as a;
    I realized after making this post that I have one more comment, and it's important for some readers.

    An alternative (correct) way to write the query is included below, the elements in the XPath expression are explicitly qualified with the namespace prefix. Notice that when the prefix is declared in the XMLNAMESPACES declaration, it is not declared with delimiters (quotes). This causes SQL to upper-case the prefix. Unlike SQL, XPath is case sensitive, so you need upper-case prefixes in the path expression.

    If we were to delimit the lower-case ("xmlns") in the XMLNAMESPACES declaration then the namespace prefix would be declared using lower-case. But lower-case xmlns cannot be used as a namespace prefix, because the prefix is reserved for namespace declarations in an XML document. So if the query were written with lower-case xmlns as a prefix, we would get an SQL error that says the namespace prefix is not valid.

    I hope that helps. If you have further questions, I would suggest posting in the DB2 for i forum. XMLTABLE is an SQL concept, rather than an RPG specific idea.

    select a.*
    FROM
    XMLTABLE(
    xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS "soap", 'http://chkenergy.iseries.com/' as xmlns),
    '$doc/soap:Envelope/soap:Body'
    PASSING
    xmlparse(document
    '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><ArtesiaGetVendorResponse xmlns="http://chkenergy.iseries.com/"><ArtesiaGetVendorResult>RAYMOND H ELROD</ArtesiaGetVendorResult></ArtesiaGetVendorResponse></soap:Body></soap:Envelope>'
    )
    as "doc"
    columns
    Vendor varchar(50) path 'XMLNS:ArtesiaGetVendorResponse/XMLNS:ArtesiaGetVendorResult'
    ) as a;
  • tcj2001
    tcj2001
    7 Posts

    Re: Parse XML with namespace using SQL

    ‏2013-03-01T02:37:54Z  
    I realized after making this post that I have one more comment, and it's important for some readers.

    An alternative (correct) way to write the query is included below, the elements in the XPath expression are explicitly qualified with the namespace prefix. Notice that when the prefix is declared in the XMLNAMESPACES declaration, it is not declared with delimiters (quotes). This causes SQL to upper-case the prefix. Unlike SQL, XPath is case sensitive, so you need upper-case prefixes in the path expression.

    If we were to delimit the lower-case ("xmlns") in the XMLNAMESPACES declaration then the namespace prefix would be declared using lower-case. But lower-case xmlns cannot be used as a namespace prefix, because the prefix is reserved for namespace declarations in an XML document. So if the query were written with lower-case xmlns as a prefix, we would get an SQL error that says the namespace prefix is not valid.

    I hope that helps. If you have further questions, I would suggest posting in the DB2 for i forum. XMLTABLE is an SQL concept, rather than an RPG specific idea.

    select a.*
    FROM
    XMLTABLE(
    xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS "soap", 'http://chkenergy.iseries.com/' as xmlns),
    '$doc/soap:Envelope/soap:Body'
    PASSING
    xmlparse(document
    '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><ArtesiaGetVendorResponse xmlns="http://chkenergy.iseries.com/"><ArtesiaGetVendorResult>RAYMOND H ELROD</ArtesiaGetVendorResult></ArtesiaGetVendorResponse></soap:Body></soap:Envelope>'
    )
    as "doc"
    columns
    Vendor varchar(50) path 'XMLNS:ArtesiaGetVendorResponse/XMLNS:ArtesiaGetVendorResult'
    ) as a;
    Thank you so much for the explanation. I have another question does ISERIES has soaphttpv or soaphttpc function to access web service, I have seen it is available for DB2 9 for z/os. Currently I have written a rpg program that uses socket API to connect to the web service and return the xml response back, I have wrapped this rpg with SQL UDF and I use this in the SQl to get the soap response.
  • NickLawrence
    NickLawrence
    18 Posts

    Re: Parse XML with namespace using SQL

    ‏2013-03-01T04:48:44Z  
    • tcj2001
    • ‏2013-03-01T02:37:54Z
    Thank you so much for the explanation. I have another question does ISERIES has soaphttpv or soaphttpc function to access web service, I have seen it is available for DB2 9 for z/os. Currently I have written a rpg program that uses socket API to connect to the web service and return the xml response back, I have wrapped this rpg with SQL UDF and I use this in the SQl to get the soap response.
    We do not support the built-in soaphttpv or soaphttpc. However we did just recently announce support for HTTP methods in SYSTOOLS that could be used to access web-services.

    The PTFs are not available yet, but you can track the status of all of the enhancements that have been announced or have already shipped post GA of DB2 for i 7.1 at https://www.ibm.com/developerworks/mydeveloperworks/wikis/home?lang=en#/wiki/IBM%20i%20Technology%20Updates/page/DB2%20for%20i%20Functional%20Enhancements

    There is a link to an article about similar HTTP function that is available for DB2 for z/OS and DB2 LUW in the DB2 for i announcement information.