Tuesday, June 17, 2008

OpenXML over WDDX (Web Distributed Data eXchange) data

DECLARE @xmlstring VARCHAR(MAX)
DECLARE @idoc int
DECLARE @doc xml

SET @xmlstring ='
YESanyJamesBall67877767689711631004117268767hhkjhkjuk
' SET @doc = CONVERT(XML,@xmlstring) EXEC sp_xml_preparedocument @idoc OUTPUT, @doc select a.id, a.localname, b.id, b.parentid, b.localname, c.id, c.parentid, c.localname, d.id, d.parentid, d.text as dataproperty, f.text as datavalue from (SELECT * FROM OPENXML (@idoc, 'wddxPacket/data/struct', 1)) a inner join (SELECT * FROM OPENXML (@idoc, 'wddxPacket/data/struct', 1)) b on b.parentid = a.id inner join (SELECT * FROM OPENXML (@idoc, 'wddxPacket/data/struct', 1)) c on c.parentid = b.id inner join (SELECT * FROM OPENXML (@idoc, 'wddxPacket/data/struct', 1)) d on d.parentid = c.id left join (SELECT * FROM OPENXML (@idoc, 'wddxPacket/data/struct', 1)) e on e.parentid = b.id left join (SELECT * FROM OPENXML (@idoc, 'wddxPacket/data/struct', 1)) f on f.parentid = e.id where c.localname = 'name' and e.localname <> 'name'

No comments: