Jump to content
Sign in to follow this  
Elvira

insert (XML DML)

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...