Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SOAP XML parsing and inserting to temp table

Answered SOAP XML parsing and inserting to temp table

  • Tuesday, February 19, 2013 5:33 AM
     
      Has Code
    Sign In to Vote
    0

    I want to parse the below XML and insert the values into table. 

    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:por="http://nc.aopm.es/v1-9/portabilidad" xmlns:v1="http://nc.aopm.es/v1-9">
   <soapenv:Header/>
   <soapenv:Body>
      <por:Parent1>
         <por:Node1>Node value1</por:Node1>
         <por:Node2>Node value2</por:Node2>        
         <por:SubNode>
            <v1:SubNode1>
               <v1:tipo>ASDFGD</v1:tipo>
               <v1:documento>QWERTY</v1:documento>            
            <v1:SubNode1/>
         </por:SubNode>         
      </por:Parent1>
   </soapenv:Body>
</soapenv:Envelope>

    Create table #table1
    (
        node1 varchar(100),
        node2 varchar(100),
        node3 varchar(100),
        tipo varchar(100),
        documento varchar(100)
    )

    Looking forward to your help..

    Thanks,
    Venkat.

    Best Regards, Venkat

     

All Replies

  • Tuesday, February 19, 2013 6:00 AM
     
     Answered Has Code
    Sign In to Vote
    1

    try: Node3 is missing in your XML 

    DECLARE @X XML ='<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:por="http://nc.aopm.es/v1-9/portabilidad" xmlns:v1="http://nc.aopm.es/v1-9">
<soapenv:Header/>
<soapenv:Body>
<por:Parent1>
<por:Node1>Node value1</por:Node1>
<por:Node2>Node value2</por:Node2>        
<por:SubNode>
<v1:SubNode1>
<v1:tipo>ASDFGD</v1:tipo>
<v1:documento>QWERTY</v1:documento>            
</v1:SubNode1>
</por:SubNode>         
</por:Parent1>
</soapenv:Body>
</soapenv:Envelope>'


;WITH  XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS soapenv , 'http://nc.aopm.es/v1-9/portabilidad' AS por , 'http://nc.aopm.es/v1-9'  AS v1)

SELECT 
x.i.value('./por:Node1[1]','VARCHAR(50)'),
x.i.value('./por:Node2[1]','VARCHAR(50)'),
NULL ,
v.i.value('./v1:tipo[1]','VARCHAR(10)'),
v.i.value('./v1:documento[1]','VARCHAR(10)')
FROM @x.nodes('/soapenv:Envelope/soapenv:Body/por:Parent1') AS x(i)
CROSS APPLY 
x.i.nodes('por:SubNode/v1:SubNode1') as v(i)

    Thanks and regards, Rishabh K

    • Proposed As Answer by Karthik D V Tuesday, February 19, 2013 6:14 AM
    • Marked As Answer by Venkats Tuesday, February 19, 2013 6:50 AM
    •  
     
  • Tuesday, February 19, 2013 6:51 AM
     
     
    Sign In to Vote
    0

    Rishabh,

    Thanks. It works great!

    Best Regards, Venkat