insert (XML DML)

[Elvira]

Hello,

I have datalist with three elements:

<root type="object">
  <DataList type="array">
    <item type="object">
      <DtoVersion type="number">1</DtoVersion>
      <RowGroup type="string">8e795a55-b670-43c7-8963-776dcf7b5bf3</RowGroup>
      <RowItem type="array">
        <item type="object">
          <Value type="string">first</Value>
        </item>
      </RowItem>
    </item>
    <item type="object">
      <DtoVersion type="number">1</DtoVersion>
      <RowGroup type="string">dcba8598-8ff5-46bf-a6ba-fe2291856229</RowGroup>
      <RowItem type="array">
        <item type="object">
          <Value type="string">second</Value>
        </item>
      </RowItem>
    </item>
    <item type="object">
      <DtoVersion type="number">1</DtoVersion>
      <RowGroup type="string">93d7327e-f088-402b-a9ec-65a4c6791aff</RowGroup>
      <RowItem type="array">
        <item type="object">
          <Value type="string">fourth</Value>
        </item>
      </RowItem>
    </item>
  </DataList>
</root>

How can I modifies the contents of an XML document, so that insert

    <item type="object">
      <DtoVersion type="number">1</DtoVersion>
      <RowGroup type="string">dcba8598-8ff5-46bf-a6ba-fe2291856229</RowGroup>
      <RowItem type="array">
        <item type="object">
          <Value type="string">third</Value>
        </item>
      </RowItem>
    </item>

BEFORE the last element?

update tblData SET

XmlData.modify('insert

<item type="object">

<DtoVersion type="number">1</DtoVersion>

<RowGroup type="string">dcba8598-8ff5-46bf-a6ba-fe2291856229</RowGroup>

<RowItem type="array">

<item type="object">

<Value type="string">third</Value>

</item>

</RowItem>

</item>

into (//DataList)[1]')

insert the last element in the list.

[justsomeguy]

What database are you using?

[Elvira]

Microsoft SQL Server 2014

[justsomeguy]

When you use insert ... into, you're telling it to make it a child of the node you refer to.  According to the documentation, you can also tell it to be the first or last node of a list, or before or after a given node.  So you probably need to identify the last node and then tell it you want to insert before that one.

https://docs.microsoft.com/en-us/sql/t-sql/xml/insert-xml-dml