XML Tricks in SQL Server 2005

Here are some stupid pet XML tricks in SQL Server 2005...

To XML-escape text stored in a column:

SELECT CAST('' AS XML).query('sql:column("v.v")')
FROM (SELECT '<>''"&' AS v) AS v

To XML-escape text stored in a variable:

DECLARE @myText AS NVARCHAR(MAX)
SELECT @myText = '<>''"&'
SELECT CAST('' AS XML).query('sql:variable("@myText")')

To extract individual nodes from XML:

SELECT result.node.query('.')
FROM (SELECT CAST('<x><a/><b/><c/></x>' AS XML) AS x) AS x
CROSS APPLY x.x.nodes('/x/*') AS result(node)

Use XML to perform concatenation aggregation:

;WITH
  strings AS (SELECT 'a' AS s UNION SELECT 'b' UNION SELECT 'c')
SELECT
  (SELECT s AS 'text()' FROM strings FOR XML PATH(''))

Generate nested XML, avoiding FOR XML EXPLICIT:

SELECT
  1 AS "@id"
, 2 AS "a/b/c"
, (SELECT v AS "e"
   FROM (SELECT 1 AS v UNION SELECT 2) AS v
   FOR XML PATH('d'), TYPE )
, 3 AS "text()"
, 4 AS "data()"
FOR XML PATH('element'), ROOT('root')

Use XML namespaces:

;WITH XMLNAMESPACES
( DEFAULT 'urn:default'
, 'urn:a' AS "a"
)
SELECT 1 AS 'a:y' FOR XML PATH('x')