Elvira

Microsoft SQL Server 2014

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.