Wednesday, July 23, 2014

SQL selecting varchar xml as nodes with namespace to produce multi-row results

Items covered below:

1.) Selecting a VARCHAR column as XML
2.) Selecting XML nodes containing a namespace
3.) Cross Apply XML column to produce multiple rows of results

TABLE example:
TestTable(
    Id INT,
    Data VARCHAR)

Data Column example:

<Props xmlns="http://tempuri.org/Test.xsd"&gt;<Type><Fun>a</Fun><Sup>1</Sup></Type><Type><Fun>b</Fun><Sup>2</Sup></Type></Props>

SQL example:
;WITH XMLNAMESPACES (N'http://tempuri.org/Test.xsd' AS NS)

SELECT
Id,

X.S.value('(NS:Fun)[1]', 'VARCHAR(8)') AS 'Fun',

X.S.value('(NS:Sup)[1]', 'VARCHAR(8)') AS 'Sup'

FROM TestTable

CROSS APPLY (SELECT CAST(Data AS XML)) AS [XML](Data)

CROSS APPLY [XML].Data.nodes('/NS:Props/NS:Type') AS X(S)