How to insert sql:column() to xml

Question

Given this xml

    <items>
  <item>
    <position>2</position>
    <code>123-122</code>
    <description>Circulator 5GPM</description>
  </item>
  <item>
    <position>4</position>
    <code>124-128</code>
    <description>Circulator 25GPM</description>
  </item>
</items>

I want to insert values from a relational table like

preferably using T-Sql sql:column("position") etc..

How would I do that ?

Answer 1

You could insert new nodes into xml like this

DECLARE @xml XML = '<items>
  <item>
    <position>2</position>
    <code>123-122</code>
    <description>Circulator 5GPM</description>
  </item>
  <item>
    <position>4</position>
    <code>124-128</code>
    <description>Circulator 25GPM</description>
  </item>
</items>'

DECLARE @SampleData AS TABLE
(
    position int,
    code varchar(20),
    [description] varchar(100)
)
INSERT INTO @SampleData
VALUES 
(1,'123-123','description 1'),
(2,'124-124','description 2')


DECLARE @NewXmlNode XML =
(
    SELECT * 
    FROM @SampleData
    FOR XML PATH('item'),TYPE
)
SELECT @xml, @NewXmlNode

SET @xml.modify('
    insert sql:variable("@NewXmlNode") as last into (/items)[1]
')

Result:

<items>
  <item>
    <position>2</position>
    <code>123-122</code>
    <description>Circulator 5GPM</description>
  </item>
  <item>
    <position>4</position>
    <code>124-128</code>
    <description>Circulator 25GPM</description>
  </item>
  <item>
    <position>1</position>
    <code>123-123</code>
    <description>description 1</description>
  </item>
  <item>
    <position>2</position>
    <code>124-124</code>
    <description>description 2</description>
  </item>
</items>