XML Data Type in SQL Server 2005

Next Post
By Admin at 5 Jan 2010, 18:06 PM
  • The XML data type (specified as XML) is a built-in data type just like varchar, int, and others. You use it the way you'd use any other SQL Server data type. Columns, variables, parameters, and values that functions return can all use the XML data type. A user can create a table that has one or more columns of type XML in addition to relational columns.
    Typed or Untyped XML

    Like other datatypes, the XML datatype must meet specific formatting criteria. When the XML data is associated with an XML schema collection, it is called "typed XML"; otherwise it is called "untyped XML". Both typed and untyped XML are accommodated within a single framework, the XML data model is preserved, and query processing enforces XML semantics.

    Example: Untyped XML

    The following statement creates a table named tblXML with a primary key column named ID and an untyped XML column named xmlVal.

    1. CREATETABLE tblXML (ID INTPRIMARYKEY, xmlVal XML notnull)

    A table can also be created with more than one XML or relational column with or without a primary key.

    Example: Typed XML

    XML columns, variables, and parameters can be bound to a collection of XML schemas. The following statement creates schema named

    mySchema,

    1. CREATE XML SCHEMA COLLECTION mySchema AS'mySchema'
    2. <schema> <element name="product"> <complextype> <sequence> <element minoccurs="0" type="string"name="id"></element> <element minoccurs="0" type="string"name="name"></element> </sequence> </complextype> </element> </schema>

    The following statement creates a table named tblXMLCatalog with an XML column Document typed using mySchema. The typed XML column is also specified to accept XML fragments, not just XML documents.

    1. CREATETABLE tblXMLCatalog (ID INTPRIMARYKEY, Document XML(CONTENT mySchema)) INSERT tblXMLCatalog VALUES(2,<span style="color: rgb(255, 0, 0);">'<product><id>1212</id><name>Product1</name></product>')</span>

    Methods on XML Data Type:


    You can retrieve entire XML values or you can retrieve parts of XML instances. This is possible by using four XML data type methods ,

    query()
    value()
    exist()
    nodes()
    modify()

    QUERY

    This method is useful for extracting parts of an XML instance. The XQuery expression evaluates to a list of XML nodes. The subtree rooted at each of these nodes is returned in document order. The result type is untyped XML.

    Example:

    1. INSERTINTO tblXML VALUES (1, <span style="color: rgb(255, 0, 0);">'<products><product><id>152</id><name>Product1</name><br><quantity>2</quantity></product><product><id>12</id><br><name>Product123</name><quantity>3</quantity></product></products>') INSERTINTO tblXML VALUES (2, '</span><items><item no="111"><name><span style="color: rgb(255, 0, 0);">Item1</span></name><amount><span style="color: rgb(255, 0, 0);">2</span></amount></item><item no="222"><name><span style="color: rgb(255, 0, 0);">Item2</span></name><amount><span style="color: rgb(255, 0, 0);">3</span></amount></item></items><span style="color: rgb(255, 0, 0);">'</span>) INSERTINTO tblXML VALUES (2, <span style="color: rgb(255, 0, 0);">'<items><item no="111"><name>Item1</name><amount>2</amount></item><br><item no="222"><name>Item2</name><amount>3</amount></item></items>'</span>)

    Run the script,

    We can read single attribute something like this,

    1. SELECT ID, xmlVal.query(<span style="color: rgb(255, 0, 0);">'/Products//product'</span>) as XMLColumn FROM tblXML

    Result set is,

    IDXMLColumn
    1152Product12
    12Product1233
    1. SELECT ID, xmlVal.query(<span style="color: rgb(255, 0, 0);">'/Products//product//id'</span>) as XMLColumn FROM tblXML

    Result set is,

    IDXMLColumn
    115212
    1. SELECT ID, xmlVal.query(<span style="color: rgb(255, 0, 0);">'/Products//product//name'</span>) as XMLColumn FROM tblXML

    Result set is,

    IDXMLColumn
    1Product1Product123
    2
    1. <span id="dnn_ctr496_MainView_ViewEntry_lblEntry"><span><span class="keyword">SELECT</span><span> ID, xmlVal.query(<span style=</span><span class="string">"color: rgb(255, 0, 0);"</span><span>></span><span class="string">'/Items//Item'</span><span></span>) </span><span class="keyword">as</span><span> XMLColumn </span><span class="keyword">FROM</span><span> tblXML </span></span></span>

    Result set is,

    IDXMLColumn
    1
    2Item12Item23
    1. SELECT ID, xmlVal.query(<span style="color: rgb(255, 0, 0);">'/Items//Item[@No = 111]'</span>) as XMLColumn FROM tblXML

    Result set is,

    IDXMLColumn
    1
    2Item12

    VALUE

    This method extracts a scalar value from an XML instance. It returns the value of the node the XQuery expression evaluates to. This value is converted to a Transact-SQL type specified as the second argument of the value() method.

    Example:

    Run the script,

    1. SELECT xmlVal.value(<span style="color: rgb(255, 0, 0);">'data((/Items//Item/name)[1])'</span>, 'varchar(100)') as XMLColumn FROM tblXML

    Result set is,

    XMLColumn
    NULL
    Item1
    1. SELECT xmlVal.value(<span style="color: rgb(255, 0, 0);">'data((/Products//product/name)[2])'</span>, 'varchar(100)') as XMLColumn FROM tblXML

    Result set is,

    XMLColumn
    Product123
    NULL

    EXIST

    This method is useful for existential checks on an XML instance. It returns 1 if the XQuery expression evaluates to non-null node list; otherwise it returns 0.

    Example:

    Run the script,

    1. SELECT * FROM tblXML

    Now the result set is,

    IDxmlVal
    1152Product12 12Product1233
    2

    Item12ItemNew23Item23

    Run the script,

    1. SELECT * FROM tblXML WHERE xmlVal.exist ('/Items//Item[@No = 222]') = 1
    IDxmlVal
    1Item12ItemNew23Item23

    NODES

    This method yields instances of a special XML data type, each of which has its context set to a different node that the XQuery expression evaluates to. The special XML data type supports the query(), value(), nodes(), and exist() methods, and can be used in count(*) aggregations and NULL checks. All other uses result in an error.

    Example:

    1. SELECT x.query('id').value('.','INT') as ID,x.query('name').value('.','varchar(100)') as Product FROM tblXML CROSS APPLY xmlVal.nodes('/Products/product') AS Tref(x)

    Result set is,

    IDProduct
    152Product1
    12Product123

    MODIFY

    This method permits modifying parts of an XML instance, such as adding or deleting subtrees, or replacing scalar values. This method allows modification of XML data and accepts an XML data modification statement as input.

    Example:

    Use the script,

    1. UPDATE tblXML SET xmlVal.modify(<span style="color: rgb(255, 0, 0);">'<br>insert<br><item no="150"><br><name>ItemNew</name><br><amount>23</amount></item><br>after (/Items//Item[@No = 111])[1]'</span>)

    Run the script,

    1. SELECT * FROM tblXML WHERE xmlVal.exist (<span style="color: rgb(255, 0, 0);">'/Items//Item[@No = 222]'</span>) = 1

    Now the result set is,

    IDxmlVal
    1Item12ItemNew23Item23

    Managing XML indexes:

    Query performance can be improved on XML columns by creating an xml index. XML indexes are created by using a new DDL statement on typed and untyped XML columns. This creates a B+tree for all XML instances in the column. The first index on an XML column is the primary XML index. Using it, three types of secondary XML indexes are supported on the XML column to speed up common classes of queries, as described in the following section.

    Primary XML Index

    XML columns are stored as binary large objects (BLOBs) in your database. Because of this, queries that search within an XML column can be slow. You can speed up these queries by creating a primary XML index on each XML column. If the base table is partitioned, the primary XML index is partitioned the same way; that is, using the same partitioning function and partitioning scheme.

    To create a primary XML index, execute the CREATE PRIMARY XML INDEX statement:

    CREATE PRIMARY XML INDEX index_name ON table_name (xml_column_name)

    Secondary XML Indexes

    In addition to the primary XML index, each XML column can have up to three secondary XML indexes—PATH, PROPERTY, and VALUE—benefit path-based queries, custom property management scenarios, and value-based queries, respectively.

    • The PATH index helps with queries that use XML path expressions. The most common case is the use of the exist() method on XML columns in the WHERE clause of a SELECT statement. The PATH index is used if queries have to be executed to retrieve data from the xml column by specifying the path and value.
    • The PROPERTY index helps with queries that retrieve particular object properties from within an XML document. This index is used to retrieve node values from the xml column by specifying a path.
    • The VALUE index helps with queries that search for values anywhere in the XML document. This index benefits queries where a node's value is known but its path is imprecisely specified in the query.
    • The ALTER INDEX statement is used to alter the table index and rebuild it.
    • The DROP INDEX can be used to drop an index.

    Comments

     

    Post a comment

    Please correct the following: