SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Category Archives: SQL Server

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

Working with XML Indexes in SQL Server

1 Comment Posted by Sourabh Agarwal on February 22, 2012

Earlier we have seen how SQL Server 2005 allows us to create XML instances in the database, using the XML Data type, introduced in SQL Server. With the introduction of SQL Server XML data types, we also have the ability to create XML indexes. For a complete details of posts related to XML, please refer here.

SQL Server allows us to create XML indexes on XML data type columns, just like it allows us to create indexes on other Data type columns. There are 2 kinds of indexes, which are allowed by SQL Server.

Primary XML indexes
Secondary XML indexes

Why do we need XML Indexes?

Columns with XML data types contain XML instances which can be fairly large (almost 2 GB), which means trying to work on XML documents (loading then in memory and then shredding them to read values) can be an expensive operation. For example, consider a table with the below mentioned columns

   1:  Create Table XMLIndex

   2:  (

   3:  Col1 int,

   4:  Col2 int,

   5:  Col3 XML (SomeXMLSchemaCollection)

   6:  )

Assuming the above table contains about 60K records, where each record contains an XML document in the XML column. Running a query like the one below,

   1:  Select Col1, Col2, Col3.query('.')

   2:  from XMLIndex

   3:  where Col3.exist('//SomeElement[.=”SomeValue”]')=1

will need to load all XML instances in Memory and then shred them to figure out the XML Instances where the Exist Condition hold true. This would be a very costly operation, leading to a lot of CPU and memory usage on SQL Server.

So if we have an application or a Database environment, where there are lot of queries running against the XML instances and it requires shredding of XML in SQL Memory, XML indexes will help. But please note that there is a cost associated with XML indexes when modifications happen on the XML instances.

Primary XML Indexes

Creating a Primary XML index, created a persistent representation of the XML instance in the XML columns. When we create a Primary index on the XML column, it will create a row-set representation of the XML. One row is created for every node in the XML.

Each row stores the following node information:

Tag name such as an element or attribute name.
Node value.
Node type such as an element node, attribute node, or text node.
Document order information, represented by an internal node identifier.
Path from each node to the root of the XML tree. This column is searched for path expressions in the query.
Primary key of the base table. The primary key of the base table is duplicated in the primary XML index for a back join with the base table.

Secondary XML Indexes

We can also create secondary XML indexes on XML Columns where a Primary XML index has already been created. There are 3 types of secondary XML Indexes which can be created in XML columns.

PATH XML Index

The PATH index is created on top of the Primary XML index and has the PATH and VALUE columns in the Primary index row-set table. So for every node, the PATH index will store the Path of that node from the root and the Value of that Node. This will be useful for queries which use PATH expressions for Iterations. The Index keys are in the following format {PATH, VALUES}.

VALUE XML Index

The VALUE index is created on top of the Primary XML index and has the VALUE and PATH columns in the Primary index row-set table. So for every node, the VALUE index will store the value of that node and the Path of the node from the Root Column. This will be useful for queries which use node values. For example columns which use the VALUE XML data type method. The Index keys are in the following order {VALUES, PATH}.

Property XML Index:

The Property Index is created on “Primary Key of base Table” along with the Path and Value columns on the Primary XML Index row-set. This can be useful in queries where the Value() method are being used, along with the Primary Key for the base table being specified in the query. For example, a query of the following kind will probably benefit from the Property index.

   1:  Select

   2:  XMLColum.query('.'), Col1, Col2

   3:  from XMLIndex

   4:  where Col1 = SomeValue and

   5:  XMLColumn.value('(/root/element1/@elementID)[1]')=10

-- Assuming that Col1 is the Primary Key for the base Table.

The Property Index keys are in the order {PKey, PATH, VALUE}.

Considerations for XML Indexes

Creating a Primary/Secondary XML Index requires certain steps to be performed on the base tables, before the XML index can be created. Some of the important considerations are

Before creating an XML Primary Index, the base table must contain a Clustered Index on the Primary Key of the table.
Primary Clustered primary key on the base table cannot be altered if we have an XML index on the base table.
Primary XML index can be created on only one XML column. Non-XML indexes cannot be created with the XML columns as on of the key column. However XML columns can be added as INCLUDE columns in non-XML indexes.
ONLINE option for XML indexes is always OFF.
XML Indexes cannot be created on XML columns in views, Table values variable or on XML variables.
When creating XML indexes SET ARITHMATIC ABORT option should be ON, and any connection trying to modify XML data must have the ARTHIMATIC ABORT ON, or else the XML methods will fail.

When altering indexes on a Table, care must be taken to perform maintenance of XML indexes separately. For example, ALTER INDEX ALL with an ONLINE=ON option will fail for tables on which XML indexes have been created, since XML indexes do not support ONLINE operations.

For dropping XML indexes, a different syntax must be used. The earlier TableName.Indexname does not work with XML indexes. We need to use the command

DROP INDEX <INDEX_NAME> ON <TABLE_NAME>

Dropping a Primary Index, will drop all secondary indexes associated with the table.

In the next posts, I will be talking about whether we should use XML indexes or not.

SQL Engine, XML SQL Server, XML, XML Indexes

Working with XML in Microsoft SQL Server

9 Comments Posted by Sourabh Agarwal on February 22, 2012

With SQL Server 2000 and later versions of Microsoft SQL Server relational tables can be queries to produce XML output or XML documents can be converted to relational tables.

In this series of articles I will talk about SQL XML features in SQL Server 2000 and then Continue with XML enhancements in SQL Server 2005 and SQL Server 2008. Some of things which I will be talking about are

SQL Engine, XML SQL Server 2000, SQL Server 2005, SQL Server 2008, XML

Newer posts →

SQLUNINTERRUPTED

Category Archives: SQL Server

XML Data type and XML Data type Methods in SQL Server

Working with XML Indexes in SQL Server

Working with XML in Microsoft SQL Server

Statistics

Archives

Categories

Meta

Author

Follow Blog via Email

Blogroll

Recent Posts

Feeds