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: