SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Tag Archives: SQL Server

XML Schema collections in SQL Server

1 Comment Posted by on February 22, 2012

Continuing with the discussion on XML, today we will talk about XML Schema collections. For a complete list of related topics, please refer here. If you are looking for a detailed course on XML Schema Definitions, please start here.

Schema collections were introduced in SQL Server 2005, and allow the user to create a collections of XML schema definitions, which can later be used define a typed XML variable, parameter or XML column. Just like a normal table, an XML Schema collection can be created, dropped or modified. SQL Server 2005/2008 provides some guidelines and limitations for creating XML schema collections and XML schema definitions. The details can be found at the following MSDN site

SQL 2005: http://msdn.microsoft.com/en-us/library/ms190665(v=SQL.90).aspx

SQL 2008: http://msdn.microsoft.com/en-us/library/ms190665(v=SQL.100).aspx

In this article we would be taking about the DDL support for creating XML Schema collections. We will also talk about what needs to be done, if one of the schema definition (xsd), needs to be changed.

Before we get on with the DDL for creating a schema collection, lets quickly figure out where in the Database are these collections created. Since AdventureWorks database already has some SchemaCollections defined, lets have a look at these.

In the AdventureWorks database browse to Programmability->Types->XML Schema Collection, we would find of the Schema collections being used with the database.

image

Each, SchemaCollection mentioned in the list contains at least one XML Schema definition, which would be used by some XML column in one of the tables in the Database. Additionally these can be used with XML variables and parameters.

DDL FOR SCHEMA COLLECTIONS

SQL Server supports Creation/deletion/modification of the SCHEMA COLLECTION objects using the following T-SQL syntax.

CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expression
DROP XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier
ALTER XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier ADD ‘Schema Component’

Once we have created an XML Schema Collection, we can then query the details using the following DMV’s

To list all the schema collection in the Database: sys.xml_schema_collections
To list the XML namespaces in the  database: sys.xml_schema_namespaces

Now lets try to create an XML schema collection in the Database

 

CREATE XML SCHEMA COLLECTION ITEMS
AS
‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://TestNameSpace.com"
    xmlns:ns="http://TestNameSpace.com"
    elementFormDefault="qualified">
<xsd:element name ="Book" >
      <xsd:complexType>
        <xsd:sequence>
              <xsd:element name="Publication" type="xsd:string"/>
              <xsd:element name="AuthorFirstName" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="AuthorLastName" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="PublishedDate" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        </xsd:sequence>
      <xsd:attribute name="BookID" type="xsd:int"/>
  </xsd:complexType>
</xsd:element>
</xsd:schema>’

Once the SchemaCollection is added to the database, we can declare typed XML columns or XML variables using this schema. For example

Declare @Var1 XML(Books)
create table BookDetails (PublicationDate Datetime, Book XML(ITEMS))

To modify the XML schema collection, we would need to take care of all the dependencies. In our example, the Book Column in Table BookDetails is currently typed against the Books Schema collection. Attempts to modify the schema (add element or attribute) will not fail, but removing an element from the schema or modifying the data type of the elements might generate an error.

— Modiying the existing schema in the Collection
ALTER XML SCHEMA COLLECTION ITEMS
ADD ‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://TestNameSpace.com"
    xmlns:ns="http://TestNameSpace.com"
    elementFormDefault="qualified">
              <xsd:element name="BookType" type="xsd:string"/>
</xsd:schema>’
— Adding a new schema to the Collection
ALTER XML SCHEMA COLLECTION ITEMS
ADD ‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://CarNameSpace.com"
    xmlns:ns="http://CarNameSpace.com"
    elementFormDefault="qualified">
<xsd:element name ="Car" >
      <xsd:complexType>
        <xsd:sequence>
              <xsd:element name="Company" type="xsd:string"/>
              <xsd:element name="Model" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="Color" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="Type" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        </xsd:sequence>
      <xsd:attribute name="EngineID" type="xsd:int"/>
  </xsd:complexType>
</xsd:element>
</xsd:schema>’

When inserting or updating the XML contents in the table, the final XML would be validated against the schema declaration in the collection. If any mismatch is found, an error will be generated. In SQL 2005, there is strict type validation is performed. There are no LAX validations allowed in SQL 2005, while in SQL 2008 LAX validations of Any, AnyType and AnyAttribute is allowed. We will talk more about these in subsequent posts.

An attempt to insert an XML which does not comply to the defined schema, generated an error. For example the below mentioned insert will generate an error as indicated…

insert into BookDetails values (getdate(),
‘<Book xmlns="http://TestNameSpace.com" BookID="TEST"> </Book>’)
—- Error Message
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: ‘TEST’. Location: /*:Book[1]/@*:BookID

XML SchemaCollections cannot be dropped while it is in use by any of the following

  1. Associated with any xml type parameter or column.
  2. Specified in any table constraints.
  3. Referenced in a schema-bound function or stored procedure.

Attempts to drop an SCHEMA COLLECTION while in use will generate an error.

DROP XML SCHEMA COLLECTION ITEMS
—– Error
Msg 6328, Level 16, State 1, Line 1
Specified collection ‘Items’ cannot be dropped because it is used by object ‘dbo.BookDetails’.

To Drop an existing XML SCHEMA COLLECTION, we need to perform the following steps.

  1. Either convert all the columns to Well-Formed Untyped XML, or drop the Columns/Tables
  2. Drop the Schema Collection
  3. Recreate the SCHEMA Collections for other schemas.

In the earlier example, to remove the BOOKS schema definitions, we would have to perform the steps as follows

1. Alter table "BookDetails" to make the "Book" column as Well-Formed
    ALTER TABLE BookDetail ALTER Column Book XML
2. Drop the XML Schema collection
    DROP XML SCHEMA COLLECTION ITEMS
3. Recrete the XML Schema Collections
    Create XML Schema Collection…..

In the next post I will talking about XML Indexes in SQL Server 2005 and SQL Server 2008. Till then happy learning.

Advertisement
SQL Engine, XML , , ,

Converting XML documents to Relational Tables using OPENXML

1 Comment Posted by on February 22, 2012

In earlier posts, I discussed how to generate XML output from relational tables using the FOR XML Queries. Today we will discuss how to generate relational table like output from an XML document using the OPENXML query format.

For a complete list of related topics, please refer here.

To generate relational like output, we need to first load the XML document and then parse it. Also we need to make sure that once we have got out desired results, we remove or unload the XML document.Thankfully SQL Server provides for 2 Stored Procedures which will do the work for us.

sp_xml_preparedocument: Reads the XML input and parses it using MSXML parser. After parsing, it creates an internal tree presentation of the nodes in the XML input. It returns a unique handle to the XML document, which can then be used by OPENXML to generate a table output and sp_xml_remove_document to remove the document.

sp_xml_removedocument: Removes the internal representation of the XML document. MSXML parsers can use a lot of SQL Server Address Space, and if the documents are removed post processing, it might lead to memory issues on SQL Server.

Using OPENXML

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='<ROOT>
<Customer CustomerID="10" ContactName="Paul Henriot">
       <Order CustomerID="10" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
          <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
          <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
       </Order>
</Customer>
<Customer CustomerID="20" ContactName="Carlos Gonzlez">
       <Order CustomerID="20" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
              <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
       </Order>
</Customer>
</ROOT>’
–Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

— Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, ‘/ROOT/Customer’,1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))

— Remove the document
EXEC sp_xml_removedocument @idoc

Before we get to the output, lets spend a few minutes on the query itself.

The first part of the query, takes an AML document and parses it using the PrepareDocument command. It returns an handle to this parsed document, which is assigned to the @idoc variable.

Later we are using the OPENXML Command to convert this parsed document into a tabular representation. In the end we use the RemoveDocument to remove the internal representation to the XML.

<<<<<<<<<<<<<<<<<< Snippet Output >>>>>>>>>>>>>

CustomerID CustomerName
10 Paul Henriot
20 Carlos Gonzlez

<<<<<<<<<<<<<<<<<< Snippet Output >>>>>>>>>>>>>>

The OPENXML is basically a rowset provider. It reads the parsed XML and then provides a rowset view of the XML.

The General syntax of the OPENXML is

OPENXML(
idoc int [in],
rowpattern nvarchar[in],
[flags byte[in]])
[WITH (SchemaDeclaration | TableName)]
)

The idoc int, variable is used to provide the Handle for the parsed XML document prepared using the sp_xml_prepared_document.

RowPattern is XPATH for the nodes in the XML document, which will form a part of the rowset output. In the above example, I have specified “/ROOT/Customer”, which means that create a rowset output for the Customer element in the XML document. If the changed the OPENXML rowpattern to as mentioned below, we will get all the ORDER elements as rowset.

SELECT    *
FROM       OPENXML (@idoc, ‘/ROOT/Customer/Order’,1)
            WITH (CustomerID  varchar(10),
          EmployeeID int,
                  OrderDate DATETIME)

FLAGS: The flags parameter is used to indicate the mapping that should be used between the XML data and the relational rowset,  and how the spill-over column should be filled. flags is an optional input parameter, and can be one of these values.  
0 — Defaults to attribute-centric mapping.
1 — Use the attribute-centric mapping. Can be combined with XML_ELEMENTS; in which case, attribute-centric mapping is applied first, and then element-centric mapping is applied for all columns not yet dealt with.
2 — Use the element-centric mapping. Can be combined with XML_ATTRIBUTES; in which case, attribute-centric mapping is applied first, and then element-centric mapping is applied for all columns not yet dealt with.
8 — Can be combined (logical OR) with XML_ATTRIBUTES or XML_ELEMENTS.

Lastly, we have the WITH clause, which defines the schema for the rowset output. We can either specify the details of the columns to be used, for we can provide a TableName, if the table with the desired schema already exists.

In SQL 2005 and SQL 2008, the OPENXML and sp_xml_preparedocument have been enhanced.

OPENXML in the WITH clause support has been added to specify the new data types introduced in SQL.

sp_xml_prepare_document can now accept input in the form off an XML data type variable or an XML column.

Please Note: In SQL 2005 and 2008, support was added for usage of XQuery to play with XML. I would recommended using XQquery nodes() mehod when trying to parse an XML document. Nodes() method typically performs better than OPENXML, in terms of CPU and memory usage.

I will be discussing usage of XQuery support for XML in subsequent posts.

SQL Engine, XML , ,

XML Schema collections in SQL Server

Leave a comment Posted by on February 22, 2012

Continuing with the discussion on XML, today we will talk about XML Schema collections. For a complete list of related topics, please refer here. If you are looking for a detailed course on XML Schema Definitions, please start here.

Schema collections were introduced in SQL Server 2005, and allow the user to create a collections of XML schema definitions, which can later be used define a typed XML variable, parameter or XML column. Just like a normal table, an XML Schema collection can be created, dropped or modified. SQL Server 2005/2008 provides some guidelines and limitations for creating XML schema collections and XML schema definitions. The details can be found at the following MSDN site

SQL 2005: http://msdn.microsoft.com/en-us/library/ms190665(v=SQL.90).aspx

SQL 2008: http://msdn.microsoft.com/en-us/library/ms190665(v=SQL.100).aspx

In this article we would be taking about the DDL support for creating XML Schema collections. We will also talk about what needs to be done, if one of the schema definition (xsd), needs to be changed.

Before we get on with the DDL for creating a schema collection, lets quickly figure out where in the Database are these collections created. Since AdventureWorks database already has some SchemaCollections defined, lets have a look at these.

In the AdventureWorks database browse to Programmability->Types->XML Schema Collection, we would find of the Schema collections being used with the database.

image

Each, SchemaCollection mentioned in the list contains at least one XML Schema definition, which would be used by some XML column in one of the tables in the Database. Additionally these can be used with XML variables and parameters.

DDL FOR SCHEMA COLLECTIONS

SQL Server supports Creation/deletion/modification of the SCHEMA COLLECTION objects using the following T-SQL syntax.

CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expression
DROP XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier
ALTER XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier ADD ‘Schema Component’

Once we have created an XML Schema Collection, we can then query the details using the following DMV’s

To list all the schema collection in the Database: sys.xml_schema_collections
To list the XML namespaces in the  database: sys.xml_schema_namespaces

Now lets try to create an XML schema collection in the Database

 

CREATE XML SCHEMA COLLECTION ITEMS
AS
‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://TestNameSpace.com"
    xmlns:ns="http://TestNameSpace.com"
    elementFormDefault="qualified">
<xsd:element name ="Book" >
      <xsd:complexType>
        <xsd:sequence>
              <xsd:element name="Publication" type="xsd:string"/>
              <xsd:element name="AuthorFirstName" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="AuthorLastName" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="PublishedDate" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        </xsd:sequence>
      <xsd:attribute name="BookID" type="xsd:int"/>
  </xsd:complexType>
</xsd:element>
</xsd:schema>’

Once the SchemaCollection is added to the database, we can declare typed XML columns or XML variables using this schema. For example

Declare @Var1 XML(Books)
create table BookDetails (PublicationDate Datetime, Book XML(ITEMS))

To modify the XML schema collection, we would need to take care of all the dependencies. In our example, the Book Column in Table BookDetails is currently typed against the Books Schema collection. Attempts to modify the schema (add element or attribute) will not fail, but removing an element from the schema or modifying the data type of the elements might generate an error.

— Modiying the existing schema in the Collection
ALTER XML SCHEMA COLLECTION ITEMS
ADD ‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://TestNameSpace.com"
    xmlns:ns="http://TestNameSpace.com"
    elementFormDefault="qualified">
              <xsd:element name="BookType" type="xsd:string"/>
</xsd:schema>’
— Adding a new schema to the Collection
ALTER XML SCHEMA COLLECTION ITEMS
ADD ‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://CarNameSpace.com"
    xmlns:ns="http://CarNameSpace.com"
    elementFormDefault="qualified">
<xsd:element name ="Car" >
      <xsd:complexType>
        <xsd:sequence>
              <xsd:element name="Company" type="xsd:string"/>
              <xsd:element name="Model" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="Color" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="Type" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        </xsd:sequence>
      <xsd:attribute name="EngineID" type="xsd:int"/>
  </xsd:complexType>
</xsd:element>
</xsd:schema>’

When inserting or updating the XML contents in the table, the final XML would be validated against the schema declaration in the collection. If any mismatch is found, an error will be generated. In SQL 2005, there is strict type validation is performed. There are no LAX validations allowed in SQL 2005, while in SQL 2008 LAX validations of Any, AnyType and AnyAttribute is allowed. We will talk more about these in subsequent posts.

An attempt to insert an XML which does not comply to the defined schema, generated an error. For example the below mentioned insert will generate an error as indicated…

insert into BookDetails values (getdate(),
‘<Book xmlns="http://TestNameSpace.com" BookID="TEST"> </Book>’)
—- Error Message
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: ‘TEST’. Location: /*:Book[1]/@*:BookID

XML SchemaCollections cannot be dropped while it is in use by any of the following

  1. Associated with any xml type parameter or column.
  2. Specified in any table constraints.
  3. Referenced in a schema-bound function or stored procedure.

Attempts to drop an SCHEMA COLLECTION while in use will generate an error.

DROP XML SCHEMA COLLECTION ITEMS
—– Error
Msg 6328, Level 16, State 1, Line 1
Specified collection ‘Items’ cannot be dropped because it is used by object ‘dbo.BookDetails’.

To Drop an existing XML SCHEMA COLLECTION, we need to perform the following steps.

  1. Either convert all the columns to Well-Formed Untyped XML, or drop the Columns/Tables
  2. Drop the Schema Collection
  3. Recreate the SCHEMA Collections for other schemas.

In the earlier example, to remove the BOOKS schema definitions, we would have to perform the steps as follows

1. Alter table "BookDetails" to make the "Book" column as Well-Formed
    ALTER TABLE BookDetail ALTER Column Book XML
2. Drop the XML Schema collection
    DROP XML SCHEMA COLLECTION ITEMS
3. Recrete the XML Schema Collections
    Create XML Schema Collection…..

In the next post I will talking about XML Indexes in SQL Server 2005 and SQL Server 2008. Till then happy learning.

SQL Engine, XML , , ,

XML Data type and XML Data type Methods in SQL Server

Leave a comment Posted by 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

.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; }

WITH XMLNAMESPACES ('http://TestNameSpace.com' AS ns) 
SELECT PublicationDate,Book.query('.') as results 
FROM BookDetails WHERE Book.value('(/ns:Book/@BookID)[1]', 'int')=2

.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; }

— 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

.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; }

 
-- 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)

.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; }

.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; }

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

.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; }

<<<<<<<<<<<<<<<< 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

.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; }

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

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

  <Publication>"MSPress"</Publication>

  <AuthorFirstName>"Rahul"</AuthorFirstName>

</Book>

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

Hope this will help.

SQL Engine, XML , ,

Working with XML Indexes in SQL Server

1 Comment Posted by 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.

  1. Primary XML indexes
  2. 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:  )

.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; }

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

.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; }

.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; }

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:

  1. Tag name such as an element or attribute name.
  2. Node value.
  3. Node type such as an element node, attribute node, or text node.
  4. Document order information, represented by an internal node identifier.
  5. Path from each node to the root of the XML tree. This column is searched for path expressions in the query.
  6. 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.

.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; }

.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; }

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

  1. Before creating an XML Primary Index, the base table must contain a Clustered Index on the Primary Key of the table.
  2. Primary Clustered primary key on the base table cannot be altered if we have an XML index on the base table.
  3. 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.
  4. ONLINE option for XML indexes is always OFF.
  5. XML Indexes cannot be created on XML columns in views, Table values variable or on XML variables.
  6. 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>

.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; }

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 , ,

Newer posts →