Saturday, May 19, 2007

OPENXML - Data Retrieval 2 (via CTE)

A further OPENXML example to read data from wddxPacket -

I am using recursive functionality of a sql CTE (common table expression) here -

DECLARE @idoc int
DECLARE @doc xml

SET @xmlstring ='
a string -12.456 1998-06-12T04:32:12 10 second element a string -12.456
' SET @doc = CONVERT(XML,@xmlstring) EXEC sp_xml_preparedocument @idoc OUTPUT, @doc ;with recursiveCTE(level,id,parentid,nodetype,localname,[text], sortcolumn) as ( SELECT '1',,a.parentid,a.nodetype,a.localname,a.[text] , CAST( AS VARBINARY(900)) FROM OPENXML (@idoc, 'wddxPacket/data/struct', 1) a UNION ALL SELECT '2',,b.parentid,b.nodetype,b.localname,b.[text] , CAST(sortcolumn + CAST( AS BINARY(4)) AS VARBINARY(900)) FROM OPENXML (@idoc, 'wddxPacket/data/struct', 1) b INNER JOIN recursiveCTE c on = b.parentid ) select * from recursiveCTE order by sortcolumn

No comments: