SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Tag Archives: Date types

XML Data type and XML Data type Methods in SQL Server

Leave a comment Posted by Sourabh Agarwal on February 22, 2012

Continuing with the series on XML, today we will talk about the XML Data type in SQL Server 2005 and SQL Server 2008. For a complete list of related articles please refer here.

The ability to store XML contents was introduced in Microsoft SQL Server 2005, with the addition of the XML data type and the XML data type methods. With the addition of XML as a data type, we can now create Tables with XML columns, or declare XML variables or pass XML Parameters in Stored Procedures and Functions. An XML variable declared with the XML data type can be a Typed XML( when the XML is typed against a XML schema definition) or it can be Un-Typed XML (when no schema is tied with the XML). SQL Server Schema collections allows us to define XML Schemas to be used along with the instances of XML Data type. XML Schema Collections are discussed in details in a later post.

///////////////// Declaring XML variables/Columns ////////////

Declare @Var1 XML(Books)
CREATE TABLE BookDetails (PublicationDate Datetime, Book XML(ITEMS))

In the above example, ITEMS is a XML schema collection.

With the addition of XML Data type, support was also added in form of XML Data types methods in SQL Server 2005. The following XML data types methods are available for use,

Function	Description
query() Method (xml Data Type)	Use this method to query over an XML instance.
value() Method (xml Data Type)	Retrieves a value of SQL type from an XML instance.
exist() Method (xml Data Type)	Determines whether a query returns a nonempty result.
modify() Method (xml Data Type)	Used to perform XML data manipulation
nodes() Method (xml Data Type)	Shreds XML and converts into an rowset representation.

Query Method

Usage: XML.query (‘XQuery’)

The Query method is used to specify a XQuery against the XML instance. The method takes a XML XQuery expression as an input and outputs an instance of Un-Typed XML.

Value() Method

The value method is used to retrieve the value of an element/attribute from an XML instance.

Usage: value (XQuery, SQLType)

The XQuery expression here is the path to the element or attribute whose value is to be determined. Value method requires this Path expression to be a Singleton operation, meaning that it should identify one element/attribute whose value is to be read. The SQLType, in which the data needs to be returned. SQL will try to do automatic type conversion, where possible. But where not possible a proper error message will be generated.

Exist Method

The Exist method is used to determine if a particular element or attribute exist in the XML instance.

Usage: value (XQuery)

The XQuery expression here is the path to the element or attribute which needs to be tested for Non-Empty results. The method returns the following bit values,

1, representing True, if the XQuery expression in a query returns a nonempty result.
0, representing False, if it returns an empty result.
NULL if the xml data type instance against which the query was executed contains NULL.

Modify Method

The Modify method is used to perform DML operations on the XML instance. When we need to perform Insert, Update, Deletes on the XML instance, the modify method is to be used.

Usage: Modify(XML_DML)

XML_DML is the DML (insert, update, delete) to be performed. The modify method should always be used in the SET clause of the UPDATE statement or the SET statement in case of a table variable.

Nodes Method

Nodes method functions to shred the XML instance into relations table like output. It functions similar to the OPENXML statement, but consumes lesser resources.

Usage: Nodes(Xquery) as Table(Column)

Nodes will return one row for every Node Instance in the XML document.

Now lets try to use these methods in some query,

   1:  CREATE XML SCHEMA COLLECTION Items

   2:  AS

   3:  '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

   4:      targetNamespace="http://TestNameSpace.com"

   5:      xmlns:ns="http://TestNameSpace.com"

   6:      elementFormDefault="qualified">

   7:  <xsd:element name ="Book" >

   8:        <xsd:complexType>

   9:          <xsd:sequence>

  10:            <xsd:element name="Publication" type="xsd:string"/>

  11:            <xsd:element name="AuthorFirstName" type="xsd:string" minOccurs="0" maxOccurs="1"/>

  12:            <xsd:element name="AuthorLastName" type="xsd:string" minOccurs="0" maxOccurs="1"/>

  13:            <xsd:element name="PublishedDate" type="xsd:string" minOccurs="0" maxOccurs="1"/>

  14:          </xsd:sequence>

  15:        <xsd:attribute name="BookID" type="xsd:int"/>

  16:    </xsd:complexType>

  17:  </xsd:element>

  18:  </xsd:schema>'

19:

  20:  Create table BookDetails (PublicationDate Datetime, Book XML(ITEMS))

  21:  Go

22:

  23:  insert into BookDetails values (GETDATE(),

  24:  '<Book xmlns="http://TestNameSpace.com" BookID = "1">

  25:             <Publication>"MSPress"</Publication>

  26:  </Book>')

27:

  28:  insert into BookDetails values (GETDATE(),

  29:  '<Book xmlns="http://TestNameSpace.com" BookID = "2">

  30:             <Publication>"WroxPublications"</Publication>

  31:             <AuthorFirstName>"Sourabh"</AuthorFirstName>

  32:             <AuthorLastName>"Agarwal"</AuthorLastName>

  33:  </Book>')

34:

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Above I have created a XML Schema collection, and defined a table based on the Schema collection. In the table I have inserted 2 records, we will use this table to test all the XML Data Type Methods.

— using Query() and value() method

WITH XMLNAMESPACES ('http://TestNameSpace.com' AS ns)

SELECT PublicationDate,Book.query('.') as results

FROM BookDetails WHERE Book.value('(/ns:Book/@BookID)[1]', 'int')=2

— using Query() and Exist() method

WITH XMLNAMESPACES ('http://TestNameSpace.com' AS ns)

SELECT PublicationDate,Book.query('/ns:Book/ns:Publication') as results

FROM BookDetails

WHERE Book.exist('(/ns:Book/ns:AuthorFirstName)')=0

-- using nodes() method

WITH XMLNAMESPACES ('http://TestNameSpace.com' AS ns)

SELECT PublicationDate,Book.query('/ns:Book/ns:Publication') as results

FROM BookDetails

CROSS APPLY BookDetails.Book.nodes('/ns:Book/ns:AuthorLastName') as T(Loc)

In the above queries, I am using the Value() method to find the records where the XML has a BookID of 2. Similarly I am using the Exist() method to determine which XML instances have the AuthorFirstName element.

Represented above is a special usage of the Nodes() method. With the Nodes method, the output is a Relational Table, which can be joined with existing tables or can be evaluated like a TVF.

The Usage of the Modify() method is a bit different. Modify() method is used to perform all DML’s on XML instance, which means Inserts, Updates or Deletes can be performed using the modify() method. Given below are the syntax for the commands

Syntax for Insert

insert

          Expression1 (

                     {as first | as last} into | after | before Expression2 )

Expressions is the XMl expressions which is to be inserted into the XML. If the XML is typed then care should be taken to make sure that the inserted elements comply with the XML schema.

First|Last:

Into|After|Before:

    Into: The element described by Expression1 is inserted directly as a child node to the XML identified by Expression2. If Expression2 has child nodes, then we must specify First|Last to specify where to add the element.

    After: The XML Element identified by Expression1 is inserted as sibling directly after the element identified by Expression2. Attributes cannot be specified by After.

    Before: The XML Element identified by Expression1 is inserted as sibling directly before the element identified by Expression2. Attributes cannot be specified by Before.

First|Last: Indicates where the child element has to be inserted. Used with the INTO clause.

Expression2: The Target path where Expression1 needs to be Inserted.

Syntax for delete

delete Expression

Expression: XML Element expression which needs be to be deleted.

Syntax for Update

replace value of

Expression1 with Expression2

Expression1: Old expressions which needs to be removed.

Expression2: New updated expression which needs to be added.

Now lets use these in some queries.

— Using Modify() Method

WITH XMLNAMESPACES ('http://TestNameSpace.com' AS ns)

Update BookDetails

Set Book.modify('insert <ns:AuthorFirstName>"Rahul"</ns:AuthorFirstName> as last into

    (/ns:Book)[1]')

where Book.value('(/ns:Book/@BookID)[1]', 'int')=1

WITH XMLNAMESPACES ('http://TestNameSpace.com' AS ns)

Update BookDetails

Set Book.modify('insert <ns:AuthorLastName>"Soni"</ns:AuthorLastName> after

    (/ns:Book/ns:AuthorFirstName)[1]')

where Book.value('(/ns:Book/@BookID)[1]', 'int')=1

<<<<<<<<<<<<<<<< Output >>>>>>>>>>>>>>>>>>>>>     
<Book xmlns="http://TestNameSpace.com" BookID="1">     
  <Publication>"MSPress"</Publication>      
  <AuthorFirstName>"Rahul"</AuthorFirstName>      
  <AuthorLastName>"Soni"</AuthorLastName>      
</Book>      
<<<<<<<<<<<<<<<< Output >>>>>>>>>>>>>>>>>>>>>

WITH XMLNAMESPACES ('http://TestNameSpace.com' AS ns) 
Update BookDetails 
Set Book.modify('replace value of (/ns:Book/ns:AuthorLastName)[1] with "Singh"') 
where Book.value('(/ns:Book/@BookID)[1]', 'int')=1

<<<<<<<<<<<<<<<< Output >>>>>>>>>>>>>>>>>>>>>

<Book xmlns="http://TestNameSpace.com" BookID="1">

<Publication>"MSPress"</Publication>

<AuthorFirstName>"Rahul"</AuthorFirstName>

<AuthorLastName>Singh</AuthorLastName>

</Book>

<<<<<<<<<<<<<<<< Output >>>>>>>>>>>>>>>>>>>>>

WITH XMLNAMESPACES ('http://TestNameSpace.com' AS ns)

Update BookDetails

Set Book.modify('delete /ns:Book/ns:AuthorLastName')

where Book.value('(/ns:Book/@BookID)[1]', 'int')=1

<<<<<<<<<<<<<<<< Output >>>>>>>>>>>>>>>>>>>>>

<Book xmlns="http://TestNameSpace.com" BookID="1">

<Publication>"MSPress"</Publication>

<AuthorFirstName>"Rahul"</AuthorFirstName>

</Book>

<<<<<<<<<<<<<<<< Output >>>>>>>>>>>>>>>>>>>>>

Hope this will help.

SQL Engine, XML Date types, SQL Server, XML

SQLUNINTERRUPTED

Tag Archives: Date types

XML Data type and XML Data type Methods in SQL Server

Statistics

Archives

Categories

Meta

Author

Follow Blog via Email

Blogroll

Recent Posts

Feeds