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

ISDATE Function doesn’t validate “date” and “datetime2” data types

Last week I have done some R&D stuff reading SQL Server and as well as Oracle. Recently I have come up with issue how to validate Date column. Answer is very simple; Every SQL Server guy’s Answer is ISDATE function and my answer was same. But after few second, again have a complained we can’t use this function it gives error. I got surprised and ask him to send that error with table format,

• Column data type is – date (SQL Server 2008)
• Error,

Msg 8116, Level 16, State 1, Line 3
Argument data type date is invalid for argument 1 of isdate function.

Then I have created some sample T-SQL scripts and check the issue.

USE tempdb
GO
DECLARE @smalldatetime smalldatetime
SELECT @smalldatetime='01/01/2010'
SELECT ISDATE(@smalldatetime);
GO
DECLARE @date date
SELECT @date='01/01/2010'
SELECT ISDATE(@date); --Error
GO
DECLARE @datetime datetime
SELECT @datetime='01/01/2010'
SELECT ISDATE(@datetime);
GO
DECLARE @datetime2 datetime2
SELECT @datetime2='01/01/2010'
SELECT ISDATE(@datetime2); --Error
GO

Finally I realize the problem ISDATE function, only working datetime, smalldatetime data types. Funny thing is Microsoft did not update the ISDATE () function to support this data types. Even it’s not supported SQL SERVER 2008 R2. Simply it doesn’t validate “date” data type and “datetime2” data type. After checking details for this issue Microsoft promised to consider this in there next release (I didn’t check this issue using SQL Server new CTP1 release (”Denali”))