XML data type SQL Server

Last month I have work with SQL Server and XML data. To be honest I have very limited knowledge of how XML work with SQL Server.I have post some XML Questions in MSDN forum also and take opportunity thanks them who is Answered my post. Ok... Well... with this Article I'm going to discuss XML data type,XML query and many more about XML.

Table With XML data type 
You can just create table with XML data type as follows and can insert some data too...

USE tempdb
go
IF NOT  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblXMLData]') AND [type] in (N'U')) BEGIN
    CREATE TABLE tblXMLData(xml_column xml);
END
GO
INSERT INTO tblXMLData(xml_column) VALUES('<Main>
  <child id="01" name="DatabaseUserGroup"></child>
  <child id="02" name="DatabaseUserGroup"></child>
</Main>');
GO
SELECT * FROM tblXMLData;
GO

Validate XML using XML schema

Hope now you have great idea about what is XML data type. Well next we will look-around how to combined XML schema to above XML column contains table. 

USE tempdb
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblXMLData]') AND [type] in (N'U')) BEGIN
    DROP TABLE tblXMLData;
END
GO
IF  EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[database_user_group_xml_schema]') BEGIN
    DROP XML SCHEMA COLLECTION  [dbo].[database_user_group_xml_schema];
END
GO
IF NOT EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[database_user_group_xml_schema]') BEGIN
    CREATE XML SCHEMA COLLECTION [dbo].[database_user_group_xml_schema]
        AS N'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:element name="Main">
        <xs:complexType>
          <xs:sequence>
            <xs:element maxOccurs="unbounded" name="child">
              <xs:complexType>
                <xs:attribute name="id" type="xs:unsignedByte" use="required" />
                <xs:attribute name="name" type="xs:string" use="required" />
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>';
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblXMLData]') AND [type] in (N'U')) BEGIN
    CREATE TABLE tblXMLData(xml_column xml (CONTENT [dbo].[database_user_group_xml_schema]));
END
GO
INSERT INTO tblXMLData(xml_column) VALUES('<Main>
  <child id="01" name="DatabaseUserGroup"></child>
  <child id="02" name="DatabaseUserGroup"></child>
</Main>');
GO
SELECT * FROM tblXMLData;
GO

Using above method you can validate XML using given XML schema.Following example will show you how to validate wrong XML tag,

INSERT INTO tblXMLData(xml_column) VALUES('<Main>
  <child wrongid="01" name="DatabaseUserGroup"></child>
  <child wrongid="02" name="DatabaseUserGroup"></child>
</Main>');
GO

Msg 6905, Level 16, State 3, Line 1
XML Validation: Attribute 'wrongid' is not permitted in this context. Location: /*:Main[1]/*:child[1]/@*:wrongid

 Indexes on XML Data Type Columns
Next we will move to XML Indexes, XML columns contains indexes not like other indexes in SQL Server. It's index all tags,values and paths over the XML instances. Normally XML indexes device mainly two categories such as "Primary XML Index" and "Secondary XML Indexes" following examples will explain how to create XML Indexes,

USE tempdbGO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblXMLData]') AND [type] in (N'U')) BEGIN
    DROP TABLE tblXMLData;
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblXMLData]') AND [type] in (N'U')) BEGIN
    CREATE TABLE tblXMLData(id int PRIMARY KEY,xml_column xml (CONTENT [dbo].[database_user_group_xml_schema]));
END
GO
-- Create primary index.
CREATE PRIMARY XML INDEX P_XML_IDX_TBLXMLDATA_XML_COLUMN
ON tblXMLData(xml_column)
GO
-- Create secondary indexes (PATH, VALUE, PROPERTY).
CREATE XML INDEX P_XML_IDX_TBLXMLDATA_XML_COLUMN_PATH ON tblXMLData(xml_column)
USING XML INDEX P_XML_IDX_TBLXMLDATA_XML_COLUMN
FOR PATH;
GO
CREATE XML INDEX P_XML_IDX_TBLXMLDATA_XML_COLUMN_VALUE ON tblXMLData(xml_column)
USING XML INDEX P_XML_IDX_TBLXMLDATA_XML_COLUMN
FOR VALUE;
GO
CREATE XML INDEX P_XML_IDX_TBLXMLDATA_XML_COLUMN_PROPERTY ON tblXMLData(xml_column)
USING XML INDEX P_XML_IDX_TBLXMLDATA_XML_COLUMN
FOR PROPERTY;
GO

Hope now you all have some kind of idea about XML data types. In my next article I'm going to explain how to modify XML contains data.

Cheers...