Database Reference
In-Depth Information
Using the XML Datatype
SQL Server 2012 has a datatype,
xml
, that is designed not just for holding XML documents (which are
essentially characters strings and can be stored in any character column big enough to hold them) but
also for processing XML documents. When I discussed parsing an XML document into a DOM tree, I
didn't mention that once it's parsed, the XML document can be updated. You can change element
contents and attribute values, and you can add and remove element occurrences to and from the
hierarchy.
You won't update XML documents here, but the
xml
datatype provides methods to do it. It is a very
different kind of SQL Server datatype, and describing how to exploit it would take a book of its own—
maybe more than one. The focus here will be on what every database programmer needs to know: how
to use the
xml
type to store and retrieve XML documents.
Note
There are so many ways to process XML documents (even in ADO.NET and with SQLXML, a support
package for SQL Server 2000) that only time will tell if incorporating such features into a SQL Server datatype is
worth the effort. Because XML is such an important technology, being able to process XML documents purely in T-
SQL does offer many possibilities, but right now it's unclear how much more about the
xml
data type you'll ever
need to know. At any rate, this chapter will give you what you need to know to start experimenting with it.
Try It: Creating a Table to Store XML
To create a table to hold XML documents, follow these steps:
1.
In the Object Explorer, select the previously created database SQL2012Db, and
click New Query.
Note
For the purpose of keeping AdventureWorks clean, I am using the SQL2012Db database; you may want to
execute the following statement in the same or another database.
2.
In query pane, type the following query, and click Execute:
create table xmltest
(
xid int not null primary key,
xdoc xml not null
)
