SQL XML datatype insert update delete

SQL-Server XML datatype insert update delete

XML datatype allows us to keep XML content into SQL-Server database. It's in plain text format,which can be queried using DML XML operations supported by SQL-Server 2008/2012 etc.,

1) Create Table

CREATE TABLE [dbo].[sax](
    [XMLCol] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 2)  Insert xml content.

insert into sax values('<?xml version="1.0" encoding="utf-8" ?>

<bookslist>
  <book id="ISBN-220-244" title="LINQ Unleased">
    <title tid="1">LINQ Unleased</title>
    <price>$40.99</price>
    <authors>
      <author>
        <firstname>Kimmel </firstname>
        <lastname>Paul</lastname>
      </author>
    </authors>
    <publisher>Sams</publisher>
  </book>

</bookslist>

')

bookslist is root element book node has ID to uniquely identify book and child elements.

Select * from  sax;

//output

<bookslist>
  <book id="ISBN-220-244" title="LINQ Unleased">
    <title tid="1">LINQ Unleased</title>
    <price>$40.99</price>
    <authors>
      <author>
        <firstname>Kimmel </firstname>
        <lastname>Paul</lastname>
      </author>
    </authors>
    <publisher>Sams</publisher>
  </book>
</bookslist>

select xmlcol.value('(/bookslist/book[@id="ISBN-220-244"]/title)[1]','varchar(20)') from sax;

//OUTPUT

LINQ Unleased

Step 3)  Add New book node to Existing XML

 

 update sax set xmlcol.modify(
'
  insert
  <book id="ISBN-433-234">
    <title>Beginning PHP and MySQL E-Commerce</title>
    <price>$35.99</price>
    <authors>
      <author>
        <firstname>Cristian</firstname>
        <lastname>Darie</lastname>
      </author>
      <author>
        <firstname>Emilian</firstname>
        <lastname>Balanescu</lastname>
      </author>
    </authors>
    <publisher>APress</publisher>
  </book>  as last into (/bookslist)[1]
')

Now result would be 2 book nodes 

u can query 2 books like this

select xmlcol.query('/bookslist/book[@id="ISBN-220-244"]') from sax;

select xmlcol.query('/bookslist/book[@id="ISBN-433-234"]') from sax;

Step 4)  Add Partial book details 

        like title ,price and publisher later add authors to this node.

Adding partial book details

update sax set xmlcol.modify(
'
insert
  <book id="ISBN-434-233">
    <title>Pro Entity Framework 4.0</title>
    <price>&amp;pound;29.99</price>
    <publisher>APress</publisher>
   </book>
  as last into (/bookslist)[1]
'
)

now query this book

select xmlcol.query('/bookslist/book[@id="ISBN-434-233"]') from sax; 

 step 5) Adding Author element nodes to above book(step 4)

update sax set xmlcol.modify(
'
insert
  <authors><author>
  <firstname>Scott</firstname>
  <lastname>Klein</lastname>
  </author>
  </authors>
  as last into (/bookslist/book[@id="ISBN-434-233"])[1]
'
)

Query Data using Step 4.

Step 2-5 explains how to add new xml content and append new content or insert elements into existing XML.

Step 6) XML Delete book Node

update sax set xmlcol.modify(
'
delete /bookslist/book[@id="ISBN-433-234"]
'
)

Now xmlcol has only 2 books.

OUTPUT

<bookslist>
  <book id="ISBN-220-244" title="LINQ Unleased">
    <title tid="1">LINQ Unleased</title>
    <price>$40.99</price>
    <authors>
      <author>
        <firstname>Kimmel </firstname>
        <lastname>Paul</lastname>
      </author>
    </authors>
    <publisher>Sams</publisher>
  </book>
  <book id="ISBN-434-233">
    <title>Pro Entity Framework 4.0</title>
    <price>&amp;pound;29.99</price>
    <publisher>APress</publisher>
    <authors>
      <author>
        <firstname>Scott</firstname>
        <lastname>Klein</lastname>
      </author>
    </authors>
  </book>
</bookslist>

Step 7) Replace attribute value

update sax set xmlcol.modify(
'
 replace value of (/bookslist/book/@id)[2] with
  "ISBN-434-270"
'
)

Replacing bookid ISBN-434-233  with "ISBN-434-270"

** i assumed book id  ISBN-434-233 exists at location 2.

Final OUTPUT

<bookslist>
  <book id="ISBN-434-244" title="LINQ Unleased">
    <title tid="1">LINQ Unleased</title>
    <price>$40.99</price>
    <authors>
      <author>
        <firstname>Kimmel </firstname>
        <lastname>Paul</lastname>
      </author>
    </authors>
    <publisher>Sams</publisher>
  </book>
  <book id="ISBN-434-270">
    <title>Pro Entity Framework 4.0</title>
    <price>&amp;pound;29.99</price>
    <publisher>APress</publisher>
    <authors>
      <author>
        <firstname>Scott</firstname>
        <lastname>Klein</lastname>
      </author>
    </authors>
  </book>
</bookslist>

This tutorials explains Insert,update,delete nodes in xml datatype using XML DML operations.