SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Category Archives: XML

Converting Relational Tables to XML documents using FOR XML EXPLICIT

Continuing with the series on XML, today we will talk about the FOR XML EXPLICIT clause which can be used with relation database queries to produce XML output. For a complete list of related topics, please refer here.

As mentioned above, we can use the FOR XML EXPICIT clause in the T-SQL queries to produce XML output. FOR XML has different flavors which can be used to produced desired results. Examples for SQL 2000 use the pubs/Northwind database, while SQL 2005 and the SQL 2008 examples are created over the AdventureWorks database which can be downloaded online.

Using the RAW and AUTO mode, we did not have any control over the Nesting of Elements in the XML result. The EXPLICIT mode, provides us more control over the nesting of the elements, but doing so requires a careful understanding of what is the desired nesting level and how to implement the same in your Query.

To use the EXPLICIT mode, we need to write the query in a way to produce the universal table. This table is nothing but a relational structure defining the Nesting of the Elements. Proper order by clauses have to be used to make sure that a Parent and all its children and their children are all together in the universal table.

To accomplish this, we have to always define the 1st and 2nd column in our select query as the TAG and PARENT column. The TAG column will indicate the Nest Level for each element in the XML, while the Parent column would be used to represent the Parent/Child relationship.

An example of a universal table is presented below.

Tag Parent Employee!1!EmpID EmpName!2!FName EmpName!2!LName
1 NULL 1 NULL NULL
2 1 1 Guy Gilbert
1 NULL 2 NULL NULL
2 1 2 Kevin Brown
1 NULL 3 NULL NULL
2 1 3 Roberto Tamburello
1 NULL 4 NULL NULL
2 1 4 Rob Walters
1 NULL 5 NULL NULL
2 1 5 Thierry D’Hers

When SQL Server tries to convert this output into XML, it will pick all ELEMENTS with TAG value of 1 and Parent value of 0, and make them Root Elements. Similarly, elements with TAG value 2 and Parent Value 1, and occurring immediately after the Root Element will be made a child of the Root Element, and so on.

Once again the order of data in the universal table will define the nesting. For Example consider the query below, where I have ordered the result using TAG column.

SELECT 1                    as Tag,
         NULL                 as Parent,
         Customers.CustomerID as [Customer!1!CustomerID],
         NULL                 as [Order!2!OrderID]
FROM Customers
UNION ALL
SELECT 2,
         1,
         Customers.CustomerID,
         Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY TAG

<<<<<<<<<<<<<<<<<<<<<< Universal Table Snippet >>>>>>>>>>>>>>>

TAG Parent

Customer!1!CustomerID

Order!2!OrderID

1 NULL WANDK NULL
1 NULL WARTH NULL
1 NULL WELLI NULL
1 NULL WHITC NULL
1 NULL WILMK NULL
1 NULL WOLZA NULL
2 1 ALFKI 10643
2 1 ALFKI 10692
2 1 ALFKI 10702
2 1 ALFKI 10835
2 1 ALFKI 10952

In this case, SQL will create an XML with multiple root nodes, with the Last root Node having multiple child!

Also note the naming mechanism of the columns used with EXPLICIT mode. The Columns have to named as

ElementName!TagNumber!AttributeName!directive

As with RAW and AUTO mode, EXPLICIT mode can also be used in conjunction some directives. SQL 2000 directives are listed below.

ID : An element attribute can be specified to be an ID type attribute. IDREF and IDREFS attributes can then be used to refer to them, enabling intra-document links. If XMLDATA is not requested, this keyword has no effect.

IDREF : Attributes specified as IDREF can be used to refer to ID type attributes, enabling intra-document links. If XMLDATA is not requested, this keyword has no effect.

IDREFS: Attributes specified as IDREFS can be used to refer to ID type attributes, enabling intra-document links. If XMLDATA is not requested, this keyword has no effect.

HIDE: The attribute is not displayed. This may be useful for ordering the result by an attribute that will not appear in the result.

ELEMENT: This does not generate an attribute. Instead it generates a contained element with the specified name (or generate contained element directly if no attribute name is specified). The contained data is encoded as an entity (for example, the < character becomes &lt;). This keyword can be combined with ID, IDREF, or IDREFS.

XML: This is the same as an element directive except that no entity encoding takes place (for example, the < character remains <). This directive is not allowed with any other directive except hide.

XMLTEXT: The column content should be wrapped in a single tag that will be integrated with the rest of the document. This directive is useful in fetching overflow (unconsumed) XML data stored in a column by OPENXML. If AttributeName is specified, the tag name is replaced by the specified name; otherwise, the attribute is appended to the current list of attributes of the enclosing elements and by putting the content at the beginning of the containment without entity encoding. The column with this directive must be a text type (varchar, nvarchar, char, nchar, text, ntext). This directive can be used only with hide. This directive is useful in fetching overflow data stored in a column. If the content is not a well-formed XML, the behavior is undefined.

CDATA: Contains the data by wrapping it with a CDATA section. The content is not entity encoded. The original data type must be a text type (varchar, nvarchar, text, ntext). This directive can be used only with hide. When this directive is used, AttributeName must not be specified.

I will be using some of these directives in a single query to display the results.

SELECT 1                      as Tag,
         NULL                 as Parent,
         Customers.CustomerID as [Customer!1!CustomerID!ID],
     NULL               as [CustomerName!2!Name!IDREF],
     NULL                  as [CustomerName!2!Phone!Hide],
         NULL                 as [Order!3!OrderID],
     NULL              as [Order!3!OrderDate!ELEMENT]
FROM Customers
UNION ALL
SELECT 2,
         1,
         Customers.CustomerID,
     Customers.ContactName,
     Customers.Phone,   
         NULL,
     NULL
FROM Customers
UNION ALL
SELECT 3,
    2,
    Customers.CustomerID,
    Customers.ContactName,
    NULL,
    Orders.OrderID,
    Orders.OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY [Customer!1!CustomerID!ID],[CustomerName!2!Name!IDREF], [Order!3!OrderID]
FOR XML EXPLICIT, XMLDATA

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

<Schema name="Schema5" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="Customer" content="mixed" model="open">
        <AttributeType name="CustomerID" dt:type="id"/>
        <attribute type="CustomerID"/>
</ElementType>
<ElementType name="CustomerName" content="mixed" model="open">
    <AttributeType name="Name" dt:type="idref"/><attribute type="Name"/>
</ElementType>
<ElementType name="Order" content="mixed" model="open">
    <AttributeType name="OrderID" dt:type="i4"/><attribute type="OrderID"/>
<Element type="OrderDate"/></ElementType>
    <ElementType name="OrderDate" content="textOnly" model="closed" dt:type="dateTime"/>
    </ElementType>
</ElementType>
</Schema>
<Customer xmlns="x-schema:#Schema5" CustomerID="ALFKI">
    <CustomerName Name="Maria Anders">
        <Order OrderID="10643">
            <OrderDate>1997-08-25T00:00:00</OrderDate>
        </Order>
        <Order OrderID="10692">
            <OrderDate>1997-10-03T00:00:00</OrderDate>
        </Order>
        <Order OrderID="10702">
            <OrderDate>1997-10-13T00:00:00</OrderDate>
        </Order>
        <Order OrderID="10835">
            <OrderDate>1998-01-15T00:00:00</OrderDate>
        </Order>

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

The same directives and constructs can be used with SQL 2005 and SQL 2008.

Converting Relational Tables to XML documents using FOR XML AUTO

Continuing with the series on XML, today we will talk about the FOR XML AUTO clause which can be used with relation database queries to produce XML output. For a complete list of related topics, please refer here.

As mentioned above, we can use the FOR XML AUTO clause in the T-SQL queries to produce XML output. FOR XML has different flavors which can be used to produced desired results. Examples for SQL 2000 use the pubs database, while those for SQL 2005 and the SQL 2008 examples are created over the AdventureWorks database which can be downloaded online.

FOR XML AUTO Mode

When we use the AUTO mode, we get an XML representation of each row in the T-SQL result set.  When using AUTO mode, SQL server tries to create a nested XML output, in case of multi-table joins. For every table, which has at lease one column from the table in the select list an element is added, correspondingly, the column for that table become the element attributes.

The hierarchy (nesting of the elements) in the result set is based on the order of tables identified by the columns specified in the SELECT clause; therefore, the order in which column names are specified in the SELECT clause is significant. The tables are identified and nested in the order in which the column names are listed in the SELECT clause. The first, leftmost table identified forms the top element in the resulting XML document. The second leftmost table (identified by columns in the SELECT statement) forms a sub-element within the top element, and so on.

The basic syntax of the FOR XML AUTO mode, is similar to the RAW mode.

SELECT <col1, col2,….>
From <table>
FOR XML AUTO, <directives,..>

SELECT Customers.CustomerID, Orders.OrderID, Customers.ContactName
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML AUTO

<<<<<<<< Snippet output >>>>>>>>

<Customers CustomerID="ALFKI" ContactName="Maria Anders">
  <Orders OrderID="10643"/>
  <Orders OrderID="10692"/>
  <Orders OrderID="10702"/>
  <Orders OrderID="10835"/>
  <Orders OrderID="10952"/>
  <Orders OrderID="11011"/>
</Customers>

<<<<<<<< Snippet output >>>>>>>>

When the Element, directive is supplied, the column values are converted to Child Elements for the Table Element as mentioned.

SELECT Customers.CustomerID, Orders.OrderID, Customers.ContactName
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML AUTO, ELEMENTS

<Customers>
    <CustomerID>ALFKI</CustomerID>
    <ContactName>Maria Anders</ContactName>
    <Orders>
        <OrderID>10643</OrderID>
    </Orders>
    <Orders>
        <OrderID>10692</OrderID>
    </Orders>
    …
</Customer>

With SQL 2005 similar enhancements were made to the XML AUTO mode. We can use the other directives with AUTO mode, like we do for RAW mode as described in my post.

Converting Relational Tables to XML documents using FOR XML RAW

Starting with SQL Server 2000 we can query Relational Tables to produce XML output. The XML produced by SQL in this case is not a well formed XML. Its basically an XML like representation of the Result set with opening and closing tags and attribute values for each element.

In this article, I will talk about the FOR XML RAW clause which can be used with relation database queries to produce XML output. For a complete list of related topics, please refer here.

As mentioned above, we can use the FOR XML clause in the T-SQL queries to produce XML output. FOR XML has different flavors which can be used to produced desired results. Examples for SQL 2000 use the pubs database, while those for SQL 2005 and the SQL 2008 examples are created over the AdventureWorks database which can be downloaded online.

FOR XML RAW Mode

When we use the RAW mode, we get an XML representation of each row in the T-SQL result set. The order in which the values are returned is exactly the same as that specified in the Select List. If you look closely at the output of the query, you would notice that there is a basic row element for each row in the output, with the column values forming the attributes.

The basic syntax for RAW mode queries is

SELECT <col1, col2,….>
From <table>
FOR XML RAW, <directives,..>

SELECT TOP 10 emp_id, FName, LName,  Hire_Date
FROM Employee 
FOR XML RAW

<<<< Snippet output >>>>>>

<row emp_id="PMA42628M" FName="Paolo" LName="Accorti" Hire_Date="1992-08-27T00:00:00"/>
<row emp_id="PSA89086M" FName="Pedro" LName="Afonso" Hire_Date="1990-12-24T00:00:00"/>

<<<< Snippet output >>>>>>

The following directives can be used in the RAW mode, when working with SQL 2000.

  1. XMLDATA: Includes the XML Schema that describes the data.
  2. BINARY BASE64: If we have an Image column in the table, it will be converted to BASE64 encoding in the XML output.

In SQL 2005, some modifications were made to the RAW mode. Some of the improvements which were added were

  1. Ability to rename the root node.
  2. ELEMENTS directive can also be used with RAW mode.
  3. XMLSCHEMA directive
  4. XSINIL directive
  5. TYPE directive

With the ELEMENTS directive, all attribute values can be converted into ELEMENT representation as shown below.

SELECT TOP 10 EmployeeID,
FirstName, LastName, EmailAddress, HireDate
FROM humanresources.Employee Employee JOIN person.contact Contact
        ON Employee.ContactID = Contact.ContactID
FOR XML RAW, ELEMENTS

<<<< Snippet output >>>>>>

<row>
  <EmployeeID>1</EmployeeID>
  <FirstName>Guy</FirstName>
  <LastName>Gilbert</LastName>
  <EmailAddress>guy1@adventure-works.com</EmailAddress>
  <HireDate>1996-07-31T00:00:00</HireDate>
</row>

<<<< Snippet output >>>>>>

The row root node can now be renamed to any name of our choice as shown below.

SELECT TOP 10 EmployeeID,
FirstName, LastName, EmailAddress, HireDate
FROM humanresources.Employee Employee JOIN person.contact Contact
        ON Employee.ContactID = Contact.ContactID
FOR XML RAW (‘EMPLOYEE’), ELEMENTS

<<<< Snippet output >>>>>>

<EMPLOYEE>
  <EmployeeID>1</EmployeeID>
  <FirstName>Guy</FirstName>
  <LastName>Gilbert</LastName>
  <EmailAddress>guy1@adventure-works.com</EmailAddress>
  <HireDate>1996-07-31T00:00:00</HireDate>
</EMPLOYEE>

<<<< Snippet output >>>>>>

The other directives and their functionality are mentioned below.

  1. XMLSCHEMA: Provides the SCHEMA definitions for the XML output.
  2. XSINIL: Would also return a value for NULL elements, indicting that the xsi:nil property is true for these elements.
  3. TYPE: Will not change the output in anyway, will just return XML type output. Which can be further assigned to an XML type variable.

XML Schema collections in SQL Server

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.

XML Data type and XML Data type Methods in SQL Server

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.