The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
16115
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...
6.2k2890
accept rate: 30%

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:

×10

question asked: 03 Nov '16, 09:07

question was seen: 113 times

last updated: 03 Nov '16, 10:01