How to parse XML using stored procedure

[Jeffreehy Talavera]

Hi.

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

1. This is the result xml:

<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>

2. This is the select part of my stored procedure.

response is my 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;

3. This is what I'm getting:


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'

4. But I also want to get the following tags:

<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.

Jeff.

[Siger Matt]

Hi Jeff,
I had an XML question awhile back and asked it on the SQL-Anywhere Forum. I've found it's a little more active, so you may try asking the same question there.

The main forum link is here:
http://sqlanywhere-forum.sap.com/

This is a search for all XML related questions:
http://sqlanywhere-forum.sap.com/search/?q=xml&Submit=search&t=question

This is the link for my question that was like yours:
http://sqlanywhere-forum.sap.com/questions/17204/xml-formatting