Insert an xm tag in xml variable

  • wasay_76

    SSC-Addicted

    Points: 457

    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: 64645

    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: 457

    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 🙂 🙂

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply