Insert an xm tag in xml variable

• wasay_76

  SSC-Addicted

  Points: 471

  More actions

  December 14, 2016 at 9:01 am

  #151489

  Declare @xmlVar xml =

  '<planBean>

  <intPlanID>1</intPlanID>

  <intStatusID>2</intStatusID>

  </planBean>'

  Declare @SuppXml =

  '<supplierGroup>

  <intPlanSupplierGroupID>2</intPlanSupplierGroupID>

  <intPlanID>1</intPlanID>

  <supplierList>

  <intSupplierID>3</intSupplierID> <intPlanSupplierGroupID>2</intPlanSupplierGroupID>

  </supplierList>

  </supplierGroup>'

  I want to insert <supplierGroup> tag in @xmlVar Like below

  '<planBean>

  <intPlanID>1</intPlanID>

  <intStatusID>2</intStatusID>

  <supplierGroup>

  <intPlanSupplierGroupID>2</intPlanSupplierGroupID>

  <intPlanID>1</intPlanID>

  <supplierList>

  <intSupplierID>3</intSupplierID><intPlanSupplierGroupID>2</intPlanSupplierGroupID>

  </supplierList>

  </supplierGroup>

  </planBean>'

• Hugo Kornelis

  SSC Guru

  Points: 64790

  More actions

  December 14, 2016 at 9:29 am

  #1917943

  That's what the XML method .modify is for. Try the code below:

  Declare @xmlVar xml =

  '<planBean>

  <intPlanID>1</intPlanID>

  <intStatusID>2</intStatusID>

  </planBean>';

  Declare @SuppXml xml =

  '<supplierGroup>

  <intPlanSupplierGroupID>2</intPlanSupplierGroupID>

  <intPlanID>1</intPlanID>

  <supplierList>

  <intSupplierID>3</intSupplierID> <intPlanSupplierGroupID>2</intPlanSupplierGroupID>

  </supplierList>

  </supplierGroup>';

  SET @xmlVar.modify('

  insert sql:variable("@SuppXml")

  as last

  into (/planBean[1])');

  SELECT @xmlVar;

  ---

  Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
  Visit my SQL Server blog: https://sqlserverfast.com/blog/
  SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
  

• wasay_76

  SSC-Addicted

  Points: 471

  More actions

  December 14, 2016 at 11:12 am

  #1917966

  Thanks so much Hugo, I've search for the same answer, only difference I want is to insert that tag in middle so use ' .. after(/planBean/intActionID)[1]'

  Thanks again much appreciated help