XML Parsing Help

• s-sql

  SSCommitted

  Points: 1970

  More actions

  June 28, 2016 at 9:25 am

  #327905

  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 segment, will be passed on parameter

  to another Store 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>'

  Appreciate any help on this, and need this to work on both SQL 2012 and 2005.

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  June 28, 2016 at 10:13 am

  #1887380

  Quick suggestion

  

  Declare @Message xml

  Declare @Proc xml

  Declare @Customer XML

  Declare @Product 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

  @Proc = @Message.query('Message/Procedure')

  ,@Customer= @Message.query('Message/Customer')

  ,@Product = @Message.query('Message/Product')

  ;

  Output

  @Proc

  <Procedure>sp_testProc</Procedure>

  @Customer

  <Customer><row><CustID>111</CustID><CustName>TestName2</CustName></row><row><CustID>222</CustID><CustName>TestName2</CustName></row></Customer>

  @Product

  <Product><ProdCode>AA</ProdCode><ProdName>TestProdAA</ProdName></Product>

• s-sql

  SSCommitted

  Points: 1970

  More actions

  June 28, 2016 at 12:04 pm

  #1887405

  Thank you. The solution worked.

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  June 28, 2016 at 2:48 pm

  #1887437

  s-sql (6/28/2016)

  ---

  Thank you. The solution worked.

  You are very welcome.