Saturday, June 6, 2015

SQL- XML DataType

The XML data type, introduced in SQL Server 2005, is a powerful construct.  When used wisely, it can provide useful extensions to SQL Server. 

Using the XML data type, we can store XML in its native format, query the data within the XML, efficiently and easily modify data within the XML without having to replace the entire contents, and index the data in the XML.

How to use XML data Type - We can use XML as any of the following:
1)  A variable
2) A parameter in a stored procedure or a user-defined function (UDF)
3) A return value from a UDF
4) A column in a table


Limitations of an XML data type
You should keep in mind that there are some limitations of XML data type. Apart of these limitations, XML data type can contain and be checked for null values, unlike other native types.
For example: directly, we can never compare one instance of an XML data type to another instance of an XML data type. If we want to compare them then we need to convert that instances to a text data types.
Another limitations of XML data type that we can not Group BY or Order BY clause  with an XML Data Type.

A few other Limitations of XML data type —in this case, when it is used as a column in a table:
1)  It cannot be used as a primary key or foreign key.
2) It cannot be declared with a UNIQUE constraint or COLLATE keyword.


Strongest Points
But these limitations don’t really affect the XML data type when it is used appropriately. The XML data type also has a rich feature set that more than compensates for these limitations.

XML Data Type as a Variable-
Simply we need to declare a variable for XML data type and set the XML value in generic piece of XML as shown below:



---- declare variable with xml data type
DECLARE @XmlData AS xml
---- set the value in xml variable in a generic piece of XML to represent basic order information
set @XmlData= '
<Orders>
<Order>
<OrderId>12345</OrderId>
<CustomerId>C00125</CustomerId>
<OrderDate>2015-06-07T14:22:27.25-05:00</OrderDate>
<OrderAmount>125.90</OrderAmount>
</Order>
<Order>
<OrderId>12346</OrderId>
<CustomerId>C12098</CustomerId>
<OrderDate>2015-06-07T14:22:27.25-08:00</OrderDate>
<OrderAmount>252.00</OrderAmount>
</Order>
</Orders>'
----- select the value from the xml variable
SELECT @XmlData

Working with XML in Tables
To create an XML object in your database, you simply specify the XML data type as you would any other type. For instance, the following Transact-SQL code creates the Resumes tables, inserts data into the table, and then retrieves data from that table:

------ create table to store xml data type values
CREATE TABLE OrdersXML
(
OrdersId int PRIMARY KEY,
OrdersDoc xml NOT NULL DEFAULT '<Orders />'
)
---- declare variable with xml data type
DECLARE @XmlData AS xml
---- set the value in xml variable in a generic piece of XML to represent basic order information
set @XmlData= '
<Orders>
<Order>
<OrderId>12345</OrderId>
<CustomerId>C00125</CustomerId>
<OrderDate>2015-06-07T14:22:27.25-05:00</OrderDate>
<OrderAmount>125.90</OrderAmount>
</Order>
</Orders>'
----- Insert values into the OrdersXML table
INSERT INTO OrdersXML (OrdersId, OrdersDoc) VALUES (1, @XmlData)
------ select value from the table now
select OrdersId, OrdersDoc from OrdersXML
OrdersId
OrdersDoc
1
<Orders><Order><OrderId>12345</OrderId><CustomerId>C00125</CustomerId><OrderDate>2015-06-07T14:22:27.25-05:00</OrderDate><OrderAmount>125.90</OrderAmount></Order></Orders>

SQL Server Schema Collections
SQL Server provides a simple way to us, How can we create your own schemas and store them in the database as database objects, and to then enforce a schema on any XML instance, including columns in tables and SQL Server variables.


Example -

CREATE XML SCHEMA COLLECTION OrdersXSD AS '
<xsd:schema
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:simpleType name="OrderAmountFloat" >
<xsd:restriction base="xsd:float" >
<xsd:minExclusive value="1.0" />
<xsd:maxInclusive value="5000.0" />
</xsd:restriction>
</xsd:simpleType>
<xsd:element name="Orders">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Order">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OrderId" type="xsd:int" />
<xsd:element name="CustomerId" type="xsd:int" />
<xsd:element name="OrderDate" type="xsd:dateTime" />
<xsd:element name="OrderAmount" type="OrderAmountFloat" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>'

SQL - FOR XML Commands

As we are well aware that SQL Server supports an enhancement to the T-SQL syntax that enables normal relational queries to output their result set as XML, using any of these four approaches:
1)  FOR XML RAW
2) FOR XML AUTO
3) FOR XML EXPLICIT
4) FOR XML PATH

No comments:

Post a Comment

Popular Posts