The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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

asked 03 Nov '16, 09:07

jeffreehy's gravatar image

jeffreehy
16125
accept rate: 0%


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='&lt; soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"&gt;. . . &lt; /soapenv:Envelope&gt;';

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

permanent link

answered 03 Nov '16, 10:00

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

edited 03 Nov '16, 10:01

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13

question asked: 03 Nov '16, 09:07

question was seen: 288 times

last updated: 03 Nov '16, 10:01