Insert an xm tag in xml variable

Question

Post reply

Insert an xm tag in xml variable

wasay_76

SSC-Addicted

Points: 461
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>'

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

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 1

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: 461 More actions · Answer 2

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