How to parse XML with multiple hierarchies nodes using stored SQL Anywhere procedure

[jeffreehy]

Hi.

I got an working sqlAnywhere stored procedure and I'm able to get (parse) the results partially.

1. This is the xml resulting from consume a Web service:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"> <soapenv:Header xmlns:v3="http://xmlns.tigo.com/RequestHeader/V3" xmlns:v2="http://xmlns.tigo.com/ParameterType/V2" xmlns:sch="http://xmlns.tigo.com/MFS/ViewBillersRequest/V1/schema"/> <soapenv:Body xmlns:v3="http://xmlns.tigo.com/RequestHeader/V3" xmlns:v2="http://xmlns.tigo.com/ParameterType/V2" xmlns:sch="http://xmlns.tigo.com/MFS/ViewBillersRequest/V1/schema"> <sch1:viewBillersResponse xmlns:sch1="http://xmlns.tigo.com/MFS/ViewBillersResponse/V1/schema"> <v31:ResponseHeader xmlns:v31="http://xmlns.tigo.com/ResponseHeader/V3"> <v31:GeneralResponse> <v31:correlationID>2367035995654813140-1533c226.1581730bed7.464e</v31:correlationID> <v31:status>OK</v31:status> <v31:code>viewbillers-5001-0000-S</v31:code> <v31:codeType>INFO</v31:codeType> <v31:description>Service completed</v31:description> </v31:GeneralResponse> </v31:ResponseHeader> <sch1:responseBody> <sch1:billers> <sch1:biller> <sch1:id>MR1108191809001</sch1:id> <sch1:code>01</sch1:code> <sch1:shortName>CABLE</sch1:shortName> <sch1:fullName>Tigo Star</sch1:fullName> <sch1:categoryCode>1</sch1:categoryCode> <sch1:categoryName>Pagos Tigo</sch1:categoryName> <sch1:partialPayment>Y</sch1:partialPayment> <sch1:viewBill>Y</sch1:viewBill> </sch1:biller> <sch1:biller> <sch1:id>MR1011181336001</sch1:id> <sch1:code>00</sch1:code> <sch1:shortName>POSTPAID</sch1:shortName> <sch1:fullName>Tigo Mobile</sch1:fullName> <sch1:categoryCode>1</sch1:categoryCode> <sch1:categoryName>Pagos Tigo</sch1:categoryName> <sch1:partialPayment>Y</sch1:partialPayment> <sch1:viewBill>Y</sch1:viewBill> </sch1:biller> </sch1:billers> </sch1:responseBody> </sch1:viewBillersResponse> </soapenv:Body> </soapenv:Envelope>

1. This is the select part of my stored procedure. response is the variable where I store de resulting xml.

SELECT * FROM OPENXML (response, '//*:biller') WITH (fullName CHAR(100) '*:fullName', ident CHAR(60) '*:id', code CHAR(2) '*:code', shortName CHAR(60) '*:shortName', categoryName char(100) '*:categoryName', partialPayment char(40) '*:partialPayment', viewBill char(5) '*:viewBill' ); select response;

1. This is the result from the stored procedure run:

fullName, ident, code, shortName, categoryName, partialPayment, viewBill

'Tigo Mobile', 'MR1011181336001', '00', 'POSTPAID', 'Pagos Tigo', 'Y', 'Y'.

'Tigo Star', 'MR1108191809001', '01', 'CABLE', 'Pagos Tigo', 'Y', 'Y'.

1. But I also want to get the following tags in the result:

          <v31:status>OK</v31:status> 
          <v31:code>viewbillers-5001-0000-S</v31:code> 
          <v31:codeType>INFO</v31:codeType> 
          <v31:description>Service completed</v31:description>

Cant anybody help me on this, I had triyed several way but only get null on this fields.

Thank You in advance.

Jef

[Former Member]

I don't consider myself to be a SOAP expert but focussing strictly on the XML and some OpenXML( ) aspects of this here is one thought I had.

I can pull the GeneralResponse out in a separate query the following example shows:

SELECT * 
FROM      OPENXML (response, '//*:GeneralResponse') 
    WITH (status CHAR(255) '*:status',       //**Note: had to guess on types and sizes here.**
       code CHAR(255) '*:code', 
       codeType   CHAR(255) '*:codeType',             
       description    varchar(1024) '*:categoryName'
        )
. . . ;

so the only missing trick here is to get both (incompatable) result sets.

You could combine this result with the earlier query using a UNION ALL but that would require you to align the columns adding missing ones and matching/faking types to align this all 'relationally' into one result set.

It would be easier to save {fetch} your "result" directly into and XML variable and run OpenXML twice in two queries {my example was using a 'created', XML variable named "response" to which I assigned your SOAP respons string directly to it} as in this fuller example:

create variable response xml;
set response='< soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">. . . < /soapenv:Envelope>';

SELECT *                      // your original query with from-clause removed
FROM      OPENXML (response, '//*:biller') 
    WITH (fullName CHAR(100) '*:fullName', 
       ident CHAR(60) '*:id', 
       code CHAR(2) '*:code', 
       shortName   CHAR(60) '*:shortName',             
       categoryName    char(100) '*:categoryName', 
       partialPayment   char(40) '*:partialPayment', 
       viewBill   char(5) '*:viewBill' );

SELECT *                       // my example query which never had a from-clause to remove
FROM      OPENXML (response, '//*:GeneralResponse') 
    WITH (status CHAR(255) '*:status', 
       code CHAR(255) '*:code', 
       codeType   CHAR(255) '*:codeType',             
       description    varchar(1024) '*:categoryName'
        );

While this will be using 2 result sets that is still cleaner than attempting to combine them in a UNION ALL.

HtH