Save xml document created by FOR XML to a variable

In SQL Server 2000, the xml created by FOR XML clause cannot be saved in server side. It has to be returned to client directly. This is very inconvient if you want to cache or persist the created xml. The solution is either cache the raw data of the xml, or let the client save the xml back to database explicitly, which cause another round trip.

 

In SQL 2005, this limitation is removed. The created xml can be saved in a variable (xml type or nvarchar(max) type), or insert to a table column with xml or nvarchar(max) data type.

 E.g.:

DECLARE @x XML

SET

@x = (

SELECT ContactID,

FirstName

,

LastName

,

AdditionalContactInfo

.query(

declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

//act:telephoneNumber/act:number’

) as MorePhoneNumbers

FROM Person.Contact

FOR XML AUTO, TYPE)

SELECT

@x

This creates an xml document and saved to the variable @x.  We can use xquery to do other processing on the xml @x:

SELECT

@x.value(‘(/Person.Contact[1]/@FirstName)[1]’,‘nvarchar(50)’) AS FirstName

SELECT

@x.query(‘/Person.Contact[1]’)

GO

 

If the xml document is created by FOR XML EXPLICIT mode, the syntax is little bit different. We have to warp all the SELECT … UNION into one subquery and then use SELECT … FROM (subquery) FOR XML EXPLICIT. Otherwise, sql server will raise error. E.g.:

 

DECLARE

@x xml

SELECT

@x=(

SELECT

Tag,Parent,[Employee!1!EmpID],[Name!2!FName],[Name!2!LName]

FROM

— use subquery

(

SELECT 1 as Tag,

NULL as Parent,

EmployeeID as [Employee!1!EmpID],

NULL as [Name!2!FName],

NULL as [Name!2!LName]

FROM

HumanResources.Employee E, Person.Contact C

WHERE

E.ContactID = C.ContactID

UNION ALL

SELECT

2 as Tag,

1

as Parent,

EmployeeID

,

FirstName

,

LastName

FROM

HumanResources.Employee E, Person.Contact C

WHERE

E.ContactID = C.ContactID

)  A

 

ORDER

BY [Employee!1!EmpID],[Name!2!FName]

FOR

XML EXPLICIT, TYPE)

SELECT

@x

 

Use xquery on the xml:

 

SELECT

@x.value(‘(/Employee[1]/Name/@FName)[1]’,‘nvarchar(50)’) AS FirstName

SELECT @x.query(‘/Employee[1]/Name’)

 

If the ‘TYPE’ directive is not used in the FOR XML clause, the above scripts will still work.

 

 

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

2 Responses to Save xml document created by FOR XML to a variable

  1. Phil says:

    Thanks for the post. The last bit (about FOR XML EXPLICIT) was just what I was looking for. I notice though that the code still works for me without the ‘TYPE’ directive. Should I keep it in anyway?

    • phe1129 says:

      Yes. I mentioned that in the end of the post. But I recommend to keep the TYPE directive to make the code consistent with FOR XML PATH, which requires the directive.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s