CODEWITHSUNDEEP
sql-serverxmlt-sqlxml-parsing
satishchandrat
satishchandrat

Reputation: 69

XML Help in TSQL

We receive a full block of XML with various segments on it.

Need help in separating XML segments into separate XML chunks into local xml variables.

The local xml variable, holding the separated XML segments, will be passed on parameter to another stored procedure.

For eg:

Declare @Message xml
set @Message  = 
'<Message>
    <Procedure>sp_testProc</Procedure>
    <Customer>
       <row>
          <CustID>111</CustID>
          <CustName>TestName2</CustName>
       </row> 
       <row>
          <CustID>222</CustID>
          <CustName>TestName2</CustName>
       </row> 
    </Customer>
    <Product>
       <ProdCode>AA</ProdCode>
       <ProdName>TestProdAA</ProdName>
    </Product>
 </Message>'

 select @Message 

 Declare @Proc xml
 Declare @Customer XML
 Declare @Product xml

 ----Need query help to extract as below, from @Message.
 set @Proc = '<Procedure>sp_testProc</Procedure>'

 set @Customer = 
     '<Customer>
         <row>
           <CustID>111</CustID>
           <CustName>TestName2</CustName>
         </row> 
         <row>
            <CustID>222</CustID>
            <CustName>TestName2</CustName>
         </row> 
       </Customer>'
   set @Product =
       '<Product>
            <ProdCode>AA</ProdCode>
            <ProdName>TestProdAA</ProdName>
       </Product>'

Upvotes: 2

Views: 37

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Your friend is called .query()

With these lines you'll get the portions separated:

Declare @Message xml
set @Message  = 
'<Message>
    <Procedure>sp_testProc</Procedure>
    <Customer>
       <row>
          <CustID>111</CustID>
          <CustName>TestName2</CustName>
       </row> 
       <row>
          <CustID>222</CustID>
          <CustName>TestName2</CustName>
       </row> 
    </Customer>
    <Product>
       <ProdCode>AA</ProdCode>
       <ProdName>TestProdAA</ProdName>
    </Product>
 </Message>';

 SELECT @Message.query('/Message/Procedure') AS TheProc
       ,@Message.query('/Message/Customer') AS TheCust
       ,@Message.query('/Message/Product') AS TheProd

UPDATE

And this is the code to fill three variables at once

 Declare @Proc xml
 Declare @Customer XML
 Declare @Product xml

     SELECT @[email protected]('/Message/Procedure')
           ,@[email protected]('/Message/Customer') 
           ,@[email protected]('/Message/Product');

SELECT @Proc;
SELECT @Customer;
SELECT @Product

Upvotes: 2

Related Questions