SQL 2005 - FOR XML enhancements

  • Article

-----------------------------------
--SQL 2005 - FOR XML Enhancements--
-----------------------------------
---FOR XML AUTO offers enhanced functionality
---In SQL 2000, often FOR XML EXPLICIT was required. This which was just nasty, and difficult to understand
--------
--ROOT--
--------
/*
Root adds a root node <tables/>
Notice the alias [table] for sys.objects gets used as the node name
*/
--Create 2 tables as an example
CREATE TABLE ExampleTable
(
[ID] int PRIMARY KEY
,[Name] nvarchar(256)
)
CREATE TABLE ExampleTable2
(
[ID] int PRIMARY KEY
,[Name] nvarchar(256)
)
SELECT
*
FROM
sys.objects [table]
WHERE
[Name] LIKE 'ExampleTable%'
FOR XML AUTO, ROOT('tables')
/*
Returns:
<tables>
<table name="ExampleTable" object_id="277576027" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" create_date="2005-12-14T17:15:21.457" modify_date="2005-12-14T17:15:21.527" is_ms_shipped="0" is_published="0" is_schema_published="0" />
<table name="ExampleTable2" object_id="309576141" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" create_date="2005-12-14T17:15:51.320" modify_date="2005-12-14T17:15:51.323" is_ms_shipped="0" is_published="0" is_schema_published="0" />
</tables>
*/

------------
--FOR PATH--
------------
/*
FOR PATH, provides more customisation. Rows are returned in Nodes which have a name defined in PATH
By default, PATH returns fields as elements. To return attributes, field names must be aliased with "@"
*/
SELECT
[name] AS [@name]
,*
FROM
sys.objects
WHERE
[Name] LIKE 'ExampleTable%'
FOR XML PATH('table'), ROOT('tables')
/*
Returns:
<tables>
<table name="ExampleTable">
<name>ExampleTable</name>
<object_id>1195151303</object_id>
........ FULL xml omitted
</table>
<table name="ExampleTable2">
<name>ExampleTable2</name>
<object_id>1227151417</object_id>
........ FULL xml omitted
</table>
</tables>
*/

-------------------------------------
--Nested Results using FOR XML PATH--
-------------------------------------
SELECT
[name] AS [@name]
,[create_date] AS [date/@cdate]
,[modify_date] AS [date/mdate]
,CAST('<mydata>some random xml</mydata>' AS xml) [*]
,(
SELECT
c.[name] AS [@name]
,c.[column_id] AS [@column_id]
,c.[is_nullable] AS [@allows_nulls]
,ty.[name] AS [@datatype]
FROM
sys.columns c
INNER JOIN sys.types ty ON c.[user_type_id] = ty.[user_type_id]
WHERE
t.[object_id] = c.[object_id]
FOR XML PATH('columns'), TYPE
)
FROM
sys.objects t
WHERE
[Name] LIKE 'ExampleTable%'
FOR XML PATH('table'), ROOT('tables')
/*
Results:
<tables>
<table name="ExampleTable">
<columns name="ID" column_id="1" allows_nulls="0" datatype="int" />
<columns name="Name" column_id="2" allows_nulls="1" datatype="nvarchar" />
</table>
<table name="ExampleTable2">
<columns name="ID" column_id="1" allows_nulls="0" datatype="int" />
<columns name="Name" column_id="2" allows_nulls="1" datatype="nvarchar" />
</table>
</tables>
*/

--------------------
--Set Xml Variable--
--------------------
/*
In 2000, it wasn't possible to set any variable to the result of a query
*/
DECLARE @xml xml
SELECT @xml =
(
SELECT TOP 3
[Name]
FROM sys.objects
FOR XML AUTO
)
SELECT @xml
/*
Results:
<sys.objects Name="sysrowsetcolumns" />
<sys.objects Name="sysrowsets" />
<sys.objects Name="sysallocunits" />
*/