Exploring XML in Yukon(SQL Server 2005)
XML (eXtensible Markup Language) as a feature have been hyped around SQL Server 2005 in a big way. XML has become one of the modes of communication for heterogeneous systems that want to share data. And this is the fundamental block of applications like Biztalk Server. Even XML’s figure out in applications like Word, InfoPath and is getting popular day by day. And storing, manipulating and retrieving XML’s from the database will be a great feature to have any day moving forward. XML is considered as a first class datatype and has been given the complete power as normal datatypes.
Even though XML’s did feature in the previous version of SQL Server 2000, I have to admit that the features were limited. They were not flexible and even to some extend non-extensible. Validating a XML to a schema, Storing and retrieving XML’s and manipulating them inside the SQL Server boundary itself are some of the features SQL Server 2005 will bring to desk. In this article we will look at some of the basic feature set we have in manipulating XML.
Creating XML column
It is very simple to create a XML column table. A typical syntax would look like:
CREATE TABLE XMLTable
(
idINT,
xDocXML
)
The XML datatype can hold upto 2GB of data. This is the limitation we will have with this datatype. But this is a huge amount of data. We need to quite reasonable whilst using this datatype. I warn here because XML fundamentally are a high verbose data. For every data represented we have two tags to denote as XML. Hence more the data more is the network latency and higher in the network packets sent. So there is fundamental performance overhead in using XML datatypes.
Typed XML Data
Under practical situation we might not just want to dump XML data into the column. We would like to validate the same before inserting. For such requirements we can use a XSD schema for validation. For the above example lets use the following XSD schema definition:
CREATE XML SCHEMA COLLECTION employeesSchemaCollection AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema elementFormDefault="unqualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:complexType name="remarks" mixed="false" >
<xsd:choiceminOccurs="0" maxOccurs="unbounded">
<xsd:element name="remark" type="xsd:string"/>
</xsd:choice>
</xsd:complexType>
<xsd:element name="employee">
<xsd:complexType mixed="false">
<xsd:sequence>
<xsd:element name="name" type="xsd:string"/>
<xsd:element name="boss" type="xsd:string"/>
<xsd:element name="department" type="xsd:string"/>
<xsd:element name="salary" type="xsd:decimal"/>
<xsd:sequence>
<xsd:element name="remarks" type="remarks" minOccurs="1" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
You can catch the definition of the same from:
select * from sys.xml_schema_collections
select name from sys.xml_namespaces
Once we have defined the schema we are just one step away from loading our table and then query the same. A typical insert statement would look like:
Insert into XMLTable
Values (1, '<employee>
<name>Vinod Kumar</name>
<boss></boss>
<department>DBA</department>
<salary>12000</salary>
<remarks>
<remark>Excellent employee</remark>
<remark>Good DBA.</remark>
</remarks>
</employee>')
Accessing XML Data
There are multiple operations that we can do with XML datatype. Let’s start with a simple query. We can use the query() method in the XQuery definition for the same.
SELECT
xDoc.query ('/employee/name')
FROM
XMLTable
This returns the name node of all the employees. In case we want to see the data alone, then we can use the Data keyword in the XQuery definition to retrieve the same.
SELECT
xDoc.query ('data(/employee/name)')
FROM
XMLTable
Now the flexibility that XML give is the repeated nodes inside a node element. In the above example and XSD definition we have multiple remark nodes. In the below query we will get the count of the remarks node as we query the employee name. A typical query would look like:
SELECT
employeeName = xDoc.query ('data(/employee/name)'),
Numremarks = xDoc.query ('count(/employee/remarks/remark)')
FROM
XMLTable
These are simple XQuery definition and properties exposed by the same. Next we will look at how we can insert update and delete XML nodes from the XML column.
Since the remarks node can take multiple remark nodes we will insert a new remark. A typical syntax for the same will look like:
-- Insert a XML Node
Update XMLTable
SET xDoc.modify('insert <remark>Here is the inserted Record</remark> after (/employee/remarks/remark)[1]')
Where id = 1
The next DML operation is to update the XML node we inserted. The XQuery syntax for the same would look like:
-- Update the node
Update XMLTable
Set xDoc.modify('update (/employee/remarks/remark)[2] to "This is the updated text"')
Where id = 1
Now we will delete the XML node we inserted. The syntax for the same would look like:
-- Delete the XML Node
Update XMLTable
Set xDoc.modify('delete /employee/remarks/remark[2]')
Where id = 1
These are just some of the operations we can do with XML datatypes. XQuery is a standard for querying the XML content. The interesting part of the same would be to load the XML data into a local variable. This feature for sure has impressed me the most. In the previous versions we were restricted of the fact that the FORXML clause output cannot be stored into local variable. Now in SQL Server 2005 this restriction is removed. Yippie … A great feature indeed with the capability of XML manipulation we enter a new dimension of programming in SQL Server. A typical code snippet for the same would be like:
Declare @myXML XML
Set @myXML = Select * from authors FORXML Auto,Elements
Isn’t this amazing. A cool feature as far as I see.
Conclusion
In this article I did show some of the cool implementation in using XML datatype in SQL Server 2005. In the subsequent articles I will try to explore some of the other features of Yukon in detail. Let me re-iterate of the fact that we have explore a subset of features in using XML datatype. The feature is tempting and amazing. All I have to say is “Yukon is cool”. You can visit my webspace for articles I have published in the past.
