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 @xmlstring VARCHAR(MAX)
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.id,a.parentid,a.nodetype,a.localname,a.[text] , CAST(a.id AS VARBINARY(900)) FROM OPENXML (@idoc, 'wddxPacket/data/struct', 1) a UNION ALL SELECT '2',b.id,b.parentid,b.nodetype,b.localname,b.[text] , CAST(sortcolumn + CAST(b.id AS BINARY(4)) AS VARBINARY(900)) FROM OPENXML (@idoc, 'wddxPacket/data/struct', 1) b INNER JOIN recursiveCTE c on c.id = b.parentid ) select * from recursiveCTE order by sortcolumn

No comments: