XML Parsing

[Former Member]

Hello All,

Below is XML which I want to parse to get data back to table format. The only challenge is, I have multiple Indicators tags, so basically, I need output as below.

TranId	RefTranId	ClientTranid	abc	rug_id	pa_required	predicated	rationale	auto_start
56542fcd786ee88a178b4569	56542fcf786ee88a178b456b	75,76	12	12	false	false		false
56542fcd786ee88a178b4569	56542fcf786ee88a178b456b	75,76	1234	123456	false	false		false

<?xml version="1.0" encoding="UTF-8"?>

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://localhost/">

   <SOAP-ENV:Body>

      <ns1:isPARequiredResponse>

         <TranId>56542fcd786ee88a178b4569</TranId>

         <RefTranId>56542fcf786ee88a178b456b</RefTranId>

         <ClientTranId>75,76</ClientTranId>

         <Indicators>

            <abc>12</abc>

            <rug_id>12</rug_id>

            <pa_required>false</pa_required>

            <predicted>false</predicted>

            <rationale />

            <auto_start>false</auto_start>

         </Indicators>

         <Indicators>

            <abc>1234</abc>

            <rug_id>123456</rug_id>

            <pa_required>false</pa_required>

            <predicted>false</predicted>

            <rationale />

            <auto_start>false</auto_start>

         </Indicators>        

      </ns1:isPARequiredResponse>

   </SOAP-ENV:Body>

</SOAP-ENV:Envelope>

Thanks in advance...

[Former Member · Accepted solution]

Hi Anal Patel,

Try following in sequence.

1)

CREATE TABLE storeIDs(

    TranID Varchar(30),

    RefTranId Varchar(30),

    ClientTranId Varchar(30)

)

CREATE TABLE storeValues(

    rug_id integer ,

    abc integer ,

    pa_required varchar(5),

    predicted varchar(5) ,

    rationale varchar(5) ,

    autostart varchar(5)

)

2)

insert into dba.storeValues

select * from openxml(

'<?xml version="1.0" encoding="UTF-8"?>

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://localhost/">

   <SOAP-ENV:Body>

      <ns1:isPARequiredResponse>

         <TranId>56542fcd786ee88a178b4569</TranId>

         <RefTranId>56542fcf786ee88a178b456b</RefTranId>

         <ClientTranId>75,76</ClientTranId>

         <Indicators>

            <abc>12</abc>

            <rug_id>12</rug_id>

            <pa_required>false</pa_required>

            <predicted>false</predicted>

            <rationale />

            <auto_start>false</auto_start>

         </Indicators>

         <Indicators>

            <abc>1234</abc>

            <rug_id>123456</rug_id>

            <pa_required>false</pa_required>

            <predicted>false</predicted>

            <rationale />

            <auto_start>false</auto_start>

         </Indicators>        

      </ns1:isPARequiredResponse>

   </SOAP-ENV:Body>

</SOAP-ENV:Envelope>', '//*:Body/*:isPARequiredResponse/Indicators')

WITH (rug_id integer 'rug_id', abc integer 'abc', pa_required varchar(5) 'pa_required', predicted varchar(5) 'predicted', rationale varchar(5) 'rationale',

autostart varchar(5) 'auto_start')

INSERT INTO DBA.storeIDs

select * from openxml(

'<?xml version="1.0" encoding="UTF-8"?>

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://localhost/">

   <SOAP-ENV:Body>

      <ns1:isPARequiredResponse>

         <TranId>56542fcd786ee88a178b4569</TranId>

         <RefTranId>56542fcf786ee88a178b456b</RefTranId>

         <ClientTranId>75,76</ClientTranId>

         <Indicators>

            <abc>12</abc>

            <rug_id>12</rug_id>

            <pa_required>false</pa_required>

            <predicted>false</predicted>

            <rationale />

            <auto_start>false</auto_start>

         </Indicators>

         <Indicators>

            <abc>1234</abc>

            <rug_id>123456</rug_id>

            <pa_required>false</pa_required>

            <predicted>false</predicted>

            <rationale />

            <auto_start>false</auto_start>

         </Indicators>        

      </ns1:isPARequiredResponse>

   </SOAP-ENV:Body>

</SOAP-ENV:Envelope>', '//*:Body/*:isPARequiredResponse')

WITH (TranID Varchar(30) 'TranId', RefTranId Varchar(30) 'RefTranId', ClientTranId Varchar(30) 'ClientTranId')

3) select * from dba.storeIDs, dba.storeValues