Blogger news

Blogger templates

Thursday, March 10, 2011

XML Sql Server

What is special about the xml datatype in SQL Server is that it is implicitly connected to an XML parser and, optionally, a validation engine. You are not simply storing text when you use an xml datatype, but rather you are parsing and potentially validating the XML content whenever you use it. In this section, we will look at the xml datatype and its implementation in tables and procedures.


As with any other datatype, you can use the xml datatype to provide a foundation for XML storage in tables and queries. Because the xml type is simply character-based storage, it has a lot in common with the other text-based datatypes. You identify XML literals with single quotes just as with other nvarchar types.The storage of XML implies the use the MAX keyword, meaning that it can potentially store about 2GB of text as a Binary Large Object (BLOB).

Defining a table, variable, or procedural parameter as XML is no different from using any
other datatype. In a CREATE TABLE statement, you would use the xml datatype in place of an nvarchar(max) to enforce well-formed XML structures. For example, the following code creates a table using an xml datatype. You can create this in any test database you like.

USE TestDB;
CREATE TABLE OrderData
(
OrderID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
OrderDetail xml
);

In this snippet, the xml datatype is used just like any other type. This statement created the
table with a BLOB storage structure to store up to 2GB of XML data for every data row. SQL Server will parse the XML on insert to ensure that it is well-formed as you insert it into the database. The following example also shows the definition of an XML-typed variable called
called @xmlOrderData.





INSERT INTO OrderData (OrderDetail)
VALUES (@xmlOrderData);
SELECT * FROM OrderData;


This code inserts a well-formed XML document into the xml datatype in the OrderDetail
table. Note that the string for the XML variable has been marked as a Unicode literal by using
the “N” prefix. The last line of the code that performs the select will return a result set containing that data.

If the XML document had not been well-formed, the result would have been different. This
is one of the important differences between the nvarchar(max) datatype and the xml datatype. If you remove the close tag for price in the preceding snippet, the assignment of the data to the variable will error, as in this slightly modified example.



In this case, the insert operation will give the error shown in the following snippet. If you
had defined the storage of the data as an nvarchar(max) instead of an xml datatype, you would not have received this error. However, you want this error to occur if the XML is not well formed.

Msg 9436, Level 16, State 1, Line 8
XML parsing: line 4, character 7, end tag does not match start tag

An interesting property of the xml datatype in SQL Server is its tolerance for the violation of
the single-root rule of well-formed XML. Normally, you would be required to have a single root
element for all XML streams. SQL Server, however, does not enforce this by default. The rationale for this is that the data rows may need to contain XML fragments that are rolled into other
XML data structures later. For example, look at this snippet:


You might expect this statement to fail because there is no root element. However, because
SQL Server allows this structure, which Microsoft calls a well-formed fragment, the statement proceeds without error.

No comments:

Post a Comment