What’s covered here:
-
Querying XML
- Retrieving attribute values
- Filtering and joining
-
Producing XML
- Complex Types and Renaming
- Attributes
- Nesting
- Nulls
- Dates
- Namespaces
- Updating XML
- Importing and Exporting XML
Welcome to the fourth dimension!
I’ve been working with SQL XML for a while and find it really useful and powerful, and also a little controversial. It’s useful because SQL XML provides a way to store and retrieve unstructured data that is in harmony with traditional table/column database structure. It’s controversial because it is unstructured data, and because it’s stored in a form of BLOB column.
BLOB columns (Binary Large Object for the uninitiated) are a way to store binaries in a sql server database and have long been viewed with extreme prejudice by DBAs everywhere. BLOBs are often used to store the text of documents, or images, or other types of files. Storing these types of files can chew up valuable SQL Server disk space, be slow to store and to retrieve, and not index efficiently. Often a DBA will encourage developers to store files outside of the database, with a path in a column that points to the external location of the file.
SQL XML has been a part of SQL Server for a while but it wasn’t until SQL 2005 and the introduction of the XML data type that SQL XML really became useful. The XML data type provides for the storage of XML in the database in a special datatype using xpath queries. These xpath queries can be mixed with traditional relational queries so that the distinction between XML data and relational data is blurred. The XML data type is a special form of BLOB that is optimized for XML, meaning that the xml is compressed and pre-parsed for optimum performance, and that updates are handled more efficiently.
Some uses of SQL XML
There are a few situations where using the xml data type is particularly useful.
Synchronization
XML is really useful if you want to move a set of data between separate, non-connected databases. I’ve used SQL XML to add configuration save/load to an application, where clicking on a button on a web page causes data associated with a process to be exported to XML. This data is stored natively in 10+ tables but I can export just the rows associated with the process as XML. The user can then load this same data into a different system, and I use SQL XML again to extract it out of the XML document and insert it into the appropriate tables in the other system’s database.I’ve also used XML in a testing scenario, where I have a scaled down test environment. I extract the set of rows associated with a test case to XML as a baseline. I can then start with a database empty except for schema, load up the test data, run the application, extract contents of the tables as XML and compare them with the baseline. If they’ve changed in a substantial way (I ignore some dates and some ids) then I know the test failed.
EDI
SQL XML is of course something to consider if you’re sending or receiving XML documents as part of an EDI process. Not only is it possible to create stored procedures that translate the XML to relational tables and vice-versa, but you also gain SQL Server’s excellent backup/restore support and support for replication. It can be really handy (though time consuming) to be able to search the last 10,000 XML documents or so you’ve received from a customer for some value.
Auditing/ArchivalLike synchronization, SQL XML may also make sense for your auditing needs. Need to keep a copy of every change to a customer/invoice/line item set where there are a dozen tables involved? Instead of having one audit table per data table, consider having a single audit table with an XML data type and a stored procedure that rolls those dozen tables up into a single invoice-centric XML document whenever an invoice is created or saved? No longer is there a need to keep triggers, audit tables and data tables in sync. When a change is made to a data table, update the stored procedure that creates the audit XML and you’re done.This approach to auditing can also extend to archival. Need to keep a record of those old invoices but don’t want them to clutter up your OLTP database? Write them to an XML table then delete the original rows from the data tables. The XML can either remain in an archival table in the database or be written to the file system.
With great power…
SQL XML is a great tool, but you shouldn’t plan to create an OLTP system based entirely on a single XML row. Nor should you use SQL XML for data that will be frequently updated, as locking in sql server is at the row level. If the exact formatting of the XML document is important, then SQL XML is not the best choice as extraneous white space is stripped from XML when it is stored by sql server, and the exact order of attributes is not preserved.
That said there are great reasons to use SQL XML. Consider SQL XML if you would like to easily join data stored in XML with data stored in relational tables. SQL Server has really powerful backup/recovery that will keep your EDI XML safe and sound and replication functions to make sure the XML at those remote locations stays in sync with the XML at headquarters. It’s nice to be able to use Transact SQL to code up both relational and XML business logic, and SQL Server’s XML validation will ensure that only well-formed XML enters your repositories.
Crossing boundaries
One of the first issues you’ll confront once you’ve made the decision to use SQL XML is how to get the XML files into sql server. There are a few ways to do this. SSIS is one way and using OPENROWSET BULK is another. A custom applications written in C# or VB.NET could be used. But there is another way. I’ve created an open-source utility called DBXMLTransfer. This command line utility can be used in conjunction with a temporary stored procedure to either import XML data into sql server, or export XML data from sql server to a physical file. DBXMLTransfer is hosted at http://dbxmltransfer.codeplex.com/
XML Consumption
I’m going to present a brief overview of working with XML documents in SQL Server. I’ll cover the basics of both consuming and producing XML, but I’m going to start here with how to query data from an XML data type.
Querying XML
Let’s start with the simplest example, just retrieving a single value from a sample XML. We’ll start with the following xml, which we’ll put in a variable.
declare @xml xml
set @xml='
<root xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<expirationdate>2011-08-04T00:00:00</expirationdate>
</outertag>
<outertag>
<id>3</id>
<namefirst>joe</namefirst>
<namelast>johnson</namelast>
<city>blakely</city>
<state>ca</state>
<zip>45123</zip>
<expirationdate>2011-08-06T00:00:00</expirationdate>
</outertag>
</root>
'
This XML has a three levels, a root, a grouping of tags and the individual value tags. See that here I am using “root/outertag” in the nodes tag to set the reference point.
select
nullif(t.p.value('(namefirst)[1]','varchar(7000)'),'')
from
@xml.nodes('root/outertag') t(p)
See that the query returns both first names specified in the XML.
sam
joe
The above example queries an xml variable. To execute the same query against an xml column in a table, you can use the “cross apply” operator. Here, we’ll start by setting up a table with an xml column. The xml column will contain the same XML as our example above.
declare @xmltable table([xmlcol] xml)
insert into @xmltable([xmlcol]) values(
'
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<expirationdate>2011-08-04T00:00:00</expirationdate>
</outertag>
<outertag>
<id>3</id>
<namefirst>joe</namefirst>
<namelast>johnson</namelast>
<city>blakely</city>
<state>ca</state>
<zip>45123</zip>
<expirationdate>2011-08-06T00:00:00</expirationdate>
</outertag>
</root>
')
Now, we use the “cross apply” operator to specify which xml column should be queried. It’s very similar to the above.
select
nullif(t.p.value('(namefirst)[1]','varchar(7000)'),'')
from
@xmltable a
cross apply a.[xmlcol].nodes('root/outertag') t(p)
sam
joe
The path value specified in the NODES function is the reference path, and the VALUES function is in reference to this path. Use “../” to go back one level, and specify the tag name to go forward one level. The VALUES function can return only one value, so you must set the reference path to the level with the most detail that you wish to return. This example below has two levels, a customer level and an item level.
declare @xmltable table([xmlcol] xml)
insert into @xmltable([xmlcol]) values(
'
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<expirationdate>2011-08-04T00:00:00</expirationdate>
<items>
<item>
<qty>1</qty>
<desc>Red widget</desc>
</item>
<item>
<qty>4</qty>
<desc>Blue widget</desc>
</item>
</items>
</outertag>
<outertag>
<id>3</id>
<namefirst>joe</namefirst>
<namelast>johnson</namelast>
<city>blakely</city>
<state>ca</state>
<zip>45123</zip>
<expirationdate>2011-08-06T00:00:00</expirationdate>
<items>
<item>
<qty>7</qty>
<desc>Blue widget</desc>
</item>
</items>
</outertag>
</root>
')
Set the reference path to the item level to retrieve all items. Note that the VALUE function for namefirst navigates backwards from the item level.
select
nullif(t.p.value('(../../namefirst)[1]','varchar(7000)'),''),
nullif(t.p.value('(qty)[1]','varchar(7000)'),''),
nullif(t.p.value('(desc)[1]','varchar(7000)'),'')
from
@xmltable a
cross apply a.[xmlcol].nodes('root/outertag/items/item') t(p)
sam 1 Red widget
sam 4 Blue widget
joe 7 Blue widget
If we modify the above query, changing the reference path to point to the outertag level, we get much different results.
select
nullif(t.p.value('(../../namefirst)[1]','varchar(7000)'),''),
nullif(t.p.value('(qty)[1]','varchar(7000)'),''),
nullif(t.p.value('(desc)[1]','varchar(7000)'),'')
from
@xmltable a
cross apply a.[xmlcol].nodes('root/outertag') t(p)
This query returns nulls because the VALUE functions have not been updated in relation to the reference path.
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
Here we’ve updated the VALUE functions so that namefirst points to the reference path and qty and desc tags.
select
nullif(t.p.value('(namefirst)[1]','varchar(7000)'),''),
nullif(t.p.value('(items/item/qty)[1]','varchar(7000)'),''),
nullif(t.p.value('(items/item/desc)[1]','varchar(7000)'),'')
from
@xmltable a
cross apply a.[xmlcol].nodes('root/outertag') t(p)
sam 1 Red widget
joe 7 Blue widget
The modified query no longer returns NULL values. However, there are only two rows now returned where before there were three. Why is this? The answer lies in the [1] parameter of the VALUE function. Since the VALUES function can return only one value, the [n] parameter specifies which value in the list should be returned. If there are fewer elements in the list than the [n] value you specify then the VALUE function will return a NULL. If we had specified [2] above for the qty and desc values (instead of [1]) we would have gotten this result.
select
nullif(t.p.value('(namefirst)[1]','varchar(7000)'),''),
nullif(t.p.value('(items/item/qty)[2]','varchar(7000)'),''),
nullif(t.p.value('(items/item/desc)[2]','varchar(7000)'),'')
from
@xmltable a
cross apply a.[xmlcol].nodes('root/outertag') t(p)
sam 4 Blue widget
joe NULL NULL
Attributes
If the value you would like to return is in an attribute rather than a tag, simply include the name of the attribute prefaced with an “@” sign.
declare @xmltable table([xmlcol] xml)
insert into @xmltable([xmlcol]) values(
'
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<person
id="2"
namefirst="sam"
namelast="smith"
city="hurdman"
state="nc"
zip="27000"
expirationdate="2011-08-04T00:00:00"
/>
</outertag>
<outertag>
<person
id="3"
namefirst="joe"
namelast="johnson"
city="blakely"
state="ca"
zip="45123"
expirationdate="2011-08-06T00:00:00"
/>
</outertag>
</root>
')
select
nullif(t.p.value('(@namefirst)[1]','varchar(7000)'),'')
from
@xmltable a
cross apply a.[xmlcol].nodes('root/outertag/person') t(p)
sam
joe
Filtering and joining
XML queries can be filtered using the WHERE clause and can be joined against relational tables or other XML columns.
declare @items table (itemid int,
itemdesc varchar(50))
insert into @items(itemid,itemdesc) values (1,'red widget')
insert into @items(itemid,itemdesc) values (2,'blue widget')
declare @xmltable table([xmlcol] xml)
insert into @xmltable([xmlcol]) values(
'
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<expirationdate>2011-08-04T00:00:00</expirationdate>
<items>
<item>
<itemid>1</itemid>
<qty>1</qty>
</item>
<item>
<itemid>2</itemid>
<qty>2</qty>
</item>
</items>
</outertag>
<outertag>
<id>3</id>
<namefirst>joe</namefirst>
<namelast>johnson</namelast>
<city>blakely</city>
<state>ca</state>
<zip>45123</zip>
<expirationdate>2011-08-06T00:00:00</expirationdate>
<items>
<item>
<itemid>2</itemid>
<qty>7</qty>
</item>
</items>
</outertag>
</root>
')
Here is an example of a join
select
t.p.value('(../../namefirst)[1]','varchar(7000)'),
t.p.value('(qty)[1]','varchar(7000)'),
b.[itemdesc]
from
@xmltable a
cross apply a.[xmlcol].nodes('root/outertag/items/item') t(p)
join @items b on b.[itemid]=t.p.value('(itemid)[1]','int')
sam 1 red widget
sam 2 blue widget
joe 7 blue widget
The same technique can be used to filter, with a where clause
select
t.p.value('(namefirst)[1]','varchar(7000)'),
t.p.value('(zip)[1]','varchar(7000)')
from
@xmltable a
cross apply a.[xmlcol].nodes('root/outertag') t(p)
where
t.p.value('(zip)[1]','varchar(7000)') between '25000' and '29000'
sam 27000
Views
If you commonly use an XML query it can, like any other query, be turned into a view.
Sample table
DECLARE @MYTABLE TABLE (
[ID] INT IDENTITY(1,1),
[NAMEFIRST] VARCHAR(50),
[NAMELAST] VARCHAR(50),
[CITY] VARCHAR(50),
[STATE] VARCHAR(2),
[ZIP] VARCHAR(50))
INSERT INTO @MYTABLE([NAMEFIRST],[NAMELAST],[CITY],[STATE],[ZIP])
VALUES('JON','JOHNSON','LAS VEGAS','PA','18000')
INSERT INTO @MYTABLE([NAMEFIRST],[NAMELAST],[CITY],[STATE],[ZIP])
VALUES('SAM','SMITH','HURDMAN','NC','27000')
The “for xml path” construct causes the results of a query to be output as XML:
SELECT
MYCOLUMNS
FROM
MYTABLE
FOR XML PATH(‘’), ELEMENTS XSINIL
The XML that will result when the above query is executed against our sample table will look like this:
<id xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">1</id>
<namefirst xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">jon</namefirst>
<namelast xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">johnson</namelast>
<city xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">las vegas</city>
<state xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">pa</state>
<zip xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">18000</zip>
<id xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2</id>
<namefirst xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">sam</namefirst>
<namelast xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">smith</namelast>
<city xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">hurdman</city>
<state xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">nc</state>
<zip xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">27000</zip>
Add a path to wrap the xml in a tag.
select
a.[id],
a.[namefirst],
a.[namelast],
a.[city],
a.[state],
a.[zip]
from
@mytable a
for xml path('outertag'),elements xsinil
And the xml will now be nicely broken out into “outertag” complex types.
<outertag xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<id>1</id>
<namefirst>jon</namefirst>
<namelast>johnson</namelast>
<city>las vegas</city>
<state>pa</state>
<zip>18000</zip>
</outertag>
<outertag xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
</outertag>
Add a root tag with root
select
a.[id],
a.[namefirst],
a.[namelast],
a.[city],
a.[state],
a.[zip]
from
@mytable a
for xml path('outertag'),root('root'), elements xsinil
Now there is a root element wrapping our complex types.
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>1</id>
<namefirst>jon</namefirst>
<namelast>johnson</namelast>
<city>las vegas</city>
<state>pa</state>
<zip>18000</zip>
</outertag>
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
</outertag>
</root>
Renaming and subelements
select
a.[id],
a.[namefirst] 'name/firstname',
a.[namelast] 'name/lastname',
a.[city],
a.[state],
a.[zip]
from
@mytable a
for xml path('outertag'),root('root'), elements xsinil
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>1</id>
<name>
<firstname>jon</firstname>
<lastname>johnson</lastname>
</name>
<city>las vegas</city>
<state>pa</state>
<zip>18000</zip>
</outertag>
<outertag>
<id>2</id>
<name>
<firstname>sam</firstname>
<lastname>smith</lastname>
</name>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
</outertag>
</root>
Attributes
select
a.[id],
a.[namefirst] 'name/@firstname',
a.[namelast] 'name/@lastname',
a.[city],
a.[state],
a.[zip]
from
@mytable a
for xml path('outertag'),root('root'), elements xsinil
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>1</id>
<name firstname="jon" lastname="johnson" />
<city>las vegas</city>
<state>pa</state>
<zip>18000</zip>
</outertag>
<outertag>
<id>2</id>
<name firstname="sam" lastname="smith" />
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
</outertag>
</root>
Nesting
We’ll start with this sample table.
declare @mytable table (
[id] int ,
[namefirst] varchar(50),
[namelast] varchar(50),
[city] varchar(50),
[state] varchar(2),
[zip] varchar(50))
insert into @mytable([id],[namefirst],[namelast],[city],[state],[zip])
values(1,'jon','johnson','las vegas','pa','18000')
insert into @mytable([id],[namefirst],[namelast],[city],[state],[zip])
values(2,'sam','smith','hurdman','nc','27000')
declare @myrelatedtable table (
[mytable_id] int,
[qty] int,
[desc] varchar(50))
insert into @myrelatedtable([mytable_id],[qty],[desc])
values(1,25,'blue widgets')
insert into @myrelatedtable([mytable_id],[qty],[desc])
values(1,12,'red widgets')
insert into @myrelatedtable([mytable_id],[qty],[desc])
values(2,35,'red widgets')
If we execute this query…
select
a.[id],
a.[namefirst],
a.[namelast],
a.[city],
a.[state],
a.[zip],
(
select
n.[qty],
n.[desc]
from
@myrelatedtable n
where
n.[mytable_id]=a.[id]
for xml path('item'),TYPE
)
from
@mytable a
for xml path('outertag'),root('root'), elements xsinil
Our results will look like this. Notice that the outertag complex type now contains a nested complex type for item. However, only the first item is included.
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>1</id>
<namefirst>jon</namefirst>
<namelast>johnson</namelast>
<city>las vegas</city>
<state>pa</state>
<zip>18000</zip>
<item>
<qty>25</qty>
<desc>blue widgets</desc>
</item>
<item>
<qty>12</qty>
<desc>red widgets</desc>
</item>
</outertag>
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<item>
<qty>35</qty>
<desc>red widgets</desc>
</item>
</outertag>
</root>
If there are multiple items, we can modify the query like this:
select
a.[id],
a.[namefirst],
a.[namelast],
a.[city],
a.[state],
a.[zip],
(
select
n.[qty],
n.[desc]
from
@myrelatedtable n
where
n.[mytable_id]=a.[id]
for xml path('item'),TYPE
) 'items'
from
@mytable a
for xml path('outertag'),root('root'), elements xsinil
Now there is an items complex type that contains multiple individual item complex types.
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>1</id>
<namefirst>jon</namefirst>
<namelast>johnson</namelast>
<city>las vegas</city>
<state>pa</state>
<zip>18000</zip>
<items>
<item>
<qty>25</qty>
<desc>blue widgets</desc>
</item>
<item>
<qty>12</qty>
<desc>red widgets</desc>
</item>
</items>
</outertag>
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<items>
<item>
<qty>35</qty>
<desc>red widgets</desc>
</item>
</items>
</outertag>
</root>
Nulls
Let’s create a table with some sample data that includes null values:
declare @mytable table (
[id] int ,
[namefirst] varchar(50),
[namelast] varchar(50),
[city] varchar(50),
[state] varchar(2),
[zip] varchar(50),
[expirationdate] datetime)
insert into @mytable([id],[namefirst],[namelast],[city],[state],[zip],[expirationdate])
values(1,'jon','johnson','las vegas','pa','18000',null)
Notice that NULL values are expressed like this: xsi:nil=”true”
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>1</id>
<namefirst>jon</namefirst>
<namelast>johnson</namelast>
<city>las vegas</city>
<state>pa</state>
<zip>18000</zip>
<expirationdate xsi:nil="true" />
</outertag>
</root>
Dates
Here is a sample table that includes some time-date data:
declare @mytable table (
[id] int ,
[namefirst] varchar(50),
[namelast] varchar(50),
[city] varchar(50),
[state] varchar(2),
[zip] varchar(50),
[expirationdate] datetime)
insert into @mytable([id],[namefirst],[namelast],[city],[state],[zip],[expirationdate])
values(2,'sam','smith','hurdman','nc','27000','8/4/2011')
When converted to XML, the dates will be in this format: 2011-08-04T00:00:00
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<expirationdate>2011-08-04T00:00:00</expirationdate>
</outertag>
</root>
Exporting with a namespace
Often it’s necessary to include a namespace when generating XML. It’s possible to do this with SQLXML.
To include a namespace, precede the query with the WITH XMLNAMESPACES command. For example:
WITH XMLNAMESPACES (‘http://www.jlion.com’ AS ns)
select
a.[id],
a.[namefirst],
a.[namelast],
a.[city],
a.[state],
a.[zip],
a.[expirationdate]
from
@mytable a for xml raw(‘outertag’),root(‘root’), elements xsinil;
Notice that the resulting XML includes the specified namespace:
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns="http://www.jlion.com">
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<expirationdate>2011-08-04T00:00:00</expirationdate>
</outertag>
</root>
Here is another way to add a namespace to an exported XML document. This way allows you to control the order in which the namespaces are listed.
declare @output xml=(
select
(
select
a.[id],
a.[namefirst],
a.[namelast],
a.[city],
a.[state],
a.[zip],
[expirationdate]
from
@mytable a
for xml raw('outertag'),root('root'), elements xsinil;
);
with XmlNamespaces(
'http://www.w3.org/2001/XMLSchema-instance' as xsi,
'uri:robot.jlion.com' as ns
)
select @output for xml path('') , root('root');
Notice that the resulting XML includes the specified namespace at the beginning:
<root xmlns:ns="http://www.jlion.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<expirationdate>2011-08-04T00:00:00</expirationdate>
</outertag>
</root>
Updates
It is possible to update XML using SQLXML. This is useful if you’re using the XML datatype to store unstructured data.
Here is a sample table that we’ll use to demonstrate UPDATE.
declare @mytable table (
[id] int ,
[namefirst] varchar(50),
[namelast] varchar(50),
[city] varchar(50),
[state] varchar(2),
[zip] varchar(50),
[expirationdate] datetime)
insert into @mytable([id],[namefirst],[namelast],[city],[state],[zip],[expirationdate])
values(2,'sam','smith','hurdman','nc','27000','8/4/2011')
insert into @mytable([id],[namefirst],[namelast],[city],[state],[zip],[expirationdate])
values(3,'joe','johnson','blakely','ca','45000','8/6/2011')
We’ll execute this query to retrieve the data as XML
declare @xml xml;
set @xml=(
select
a.[id],
a.[namefirst],
a.[namelast],
a.[city],
a.[state],
a.[zip],
[expirationdate]
from
@mytable a
for xml raw('outertag'),root('root'), elements xsinil
)
select @xml
When we do this, we’ll get this XML as a result:
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<expirationdate>2011-08-04T00:00:00</expirationdate>
</outertag>
<outertag>
<id>3</id>
<namefirst>joe</namefirst>
<namelast>johnson</namelast>
<city>blakely</city>
<state>ca</state>
<zip>45000</zip>
<expirationdate>2011-08-06T00:00:00</expirationdate>
</outertag>
</root>
This query, when appended to the above query will update the zip value of the first (“[1]”) outertag complex type to 27010.
DECLARE @newvalue varchar(50)
set @newvalue = '27010'
set @xml.modify('replace value of (/root/outertag/zip/text())[1] with sql:variable("@newvalue")');
select @xml
Notice that the second outertag complex type remains unchanged.
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27010</zip>
<expirationdate>2011-08-04T00:00:00</expirationdate>
</outertag>
<outertag>
<id>3</id>
<namefirst>joe</namefirst>
<namelast>johnson</namelast>
<city>blakely</city>
<state>ca</state>
<zip>45000</zip>
<expirationdate>2011-08-06T00:00:00</expirationdate>
</outertag>
</root>
Update example #2
Here is another example of an XML update. We’ll use a different sample data table for this one:
declare @mytable table (
[id] int ,
[namefirst] varchar(50),
[namelast] varchar(50),
[city] varchar(50),
[state] varchar(2),
[zip] varchar(50),
[expirationdate] datetime)
insert into @mytable([id],[namefirst],[namelast],[city],[state],[zip],[expirationdate])
values(2,'sam','smith','hurdman','nc','27000','8/4/2011')
insert into @mytable([id],[namefirst],[namelast],[city],[state],[zip],[expirationdate])
values(3,'joe','johnson','blakely','ca','45000','8/6/2011')
Here is the query we use to transform the above table to xml:
declare @xml xml;
set @xml=(
select
a.[id],
a.[namefirst],
a.[namelast],
a.[city],
a.[state],
a.[zip],
[expirationdate]
from
@mytable a
for xml raw('outertag'),root('root'), elements xsinil
)
select @xml
When the query is run, we get this xml.
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<expirationdate>2011-08-04T00:00:00</expirationdate>
</outertag>
<outertag>
<id>3</id>
<namefirst>joe</namefirst>
<namelast>johnson</namelast>
<city>blakely</city>
<state>ca</state>
<zip>45000</zip>
<expirationdate>2011-08-06T00:00:00</expirationdate>
</outertag>
</root>
Now, instead of replacing the value in the first node, we’ll update any zip elements containing the value 45000 so that they contain 45123 instead. Here’s the query that we’ll use:
DECLARE @oldvalue varchar(50),
@newvalue varchar(50)
set @oldvalue='45000'
set @newvalue = '45123'
set @xml.modify('replace value of (/root/outertag/zip[.=sql:variable("@oldvalue")]/text())[1] with sql:variable("@newvalue")');
select @xml
When we run the query we get this XML. Notice that the zip value of the second outertag complex type has been updated.
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<outertag>
<id>2</id>
<namefirst>sam</namefirst>
<namelast>smith</namelast>
<city>hurdman</city>
<state>nc</state>
<zip>27000</zip>
<expirationdate>2011-08-04T00:00:00</expirationdate>
</outertag>
<outertag>
<id>3</id>
<namefirst>joe</namefirst>
<namelast>johnson</namelast>
<city>blakely</city>
<state>ca</state>
<zip>45123</zip>
<expirationdate>2011-08-06T00:00:00</expirationdate>
</outertag>
</root>
Importing and exporting sql xml
Storing XML data in SQL Server is great, but getting the data in and out can be problemmatic for large XML datasets.
One of the really nice features of SQL Server XML data is the way that it is presented as a hyperlink to nicely formatted xml. If you’ve got a giant XML however, you may notice that not all data is there. Here’s a way around that, when using Microsoft SQL Server Management Studio.
- Right-click on the query pane, choose Query Options
- On the left side is a treeview. The bottom node of the tree is “results” with sub-node for “Grid”. Click on this subnode.
- When you do you’ll see a the bottom of the Query Options dialog that there is a section entitled “Maximum Characters Retrieved”. This value defaults to 2MB. Change it to “Unlimited”
- Close the the dialog.
- Right-click on the query pane again, this time click on “Results to…Results to Grid” (or press CTRL – D)
- Run your query.
- Now, right click on the hyperlink in the results pane and choose “Save Results as…”
- Type a filename and include an “.xml” extension. Save.
A caveat. I’ve used SQL XML as a convenient way to extract the entire contents of pretty big tables as a single file but when the files get really big, there’s an issue with carriage return/line feeds that affects the DotNet XMLDocument library’s ability to read the resulting XML. Here is a C# routine that I’ve used in the past to strip out these carriage return/line feeds so that the XML can be opened using XMLDocument:
private string cleanfile(string filename)
{
string retVal = filename + ".tmp";
StreamWriter sw = new StreamWriter(path: retVal, append: false);
using (StreamReader sr = new StreamReader(path: filename))
{
while (!sr.EndOfStream)
{
int c = sr.Read();
if (c != 10 && c != 13)
sw.Write((char)c);
}
}
sw.Flush();
sw.Close();
sw.Dispose();
return retVal;
}
To retrieve XML programmatically from SQL Server, I typically use a stored procedure and a C# function similar to this one.
protected string ExecuteProcToXML(OdbcConnection oCon, string ProcName, List oParms)
{
StringBuilder sXML = new StringBuilder();
OdbcCommand oCMD = new OdbcCommand(ProcName, oCon);
oCMD.CommandType = CommandType.StoredProcedure;
oCMD.CommandTimeout = 0;
foreach (OdbcParameter oParm in oParms)
oCMD.Parameters.Add(oParm);
OdbcDataReader oRdr = oCMD.ExecuteReader();
while (oRdr.Read())
sXML.Append(oRdr[0].ToString());
oRdr.Close();
oRdr.Dispose();
oCMD.Dispose();
return sXML.ToString();
}