Search

Custom Search

Tuesday, February 8, 2011

Simplest way to query XML in MSSQL

For Accessing XML nodes:

DECLARE @xml xml
SET @xml = N'
1002008-09-10
1012008-09-11
'
SELECT
doc.col.value('ponumber[1]', 'nvarchar(10)') ponumber
,doc.col.value('podate[1]', 'datetime') podate
FROM @xml.nodes('/polist/po') doc(col)

For Accessing element attributes:

DECLARE @xml xml
SET @xml = N'


'
SELECT
doc.col.value('@ponumber', 'nvarchar(10)') ponumber
,doc.col.value('@podate', 'datetime') podate

FROM @xml.nodes('/polist/po') doc(col)


For accessing XML arrays:

DECLARE @xml xml
SET @xml = N'
100
101
'
SELECT



Adsense Banner