SQL Server Performance Forum – Threads Archive
How to read top level values in xml
Hi all,<br /><root><br /><<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />1 n="A"><br /><l1 n="level1 1><br /><l2 n="Level2 1><br /><l3 n ="level3 1"></l3><br /><l3 n ="level3 2"></l3><br /><l3 n ="level3 3"></l3><br /></l2><br /></l1><br /><l1 n="level1 2><br /><l2 n="Level2 2><br /><l3 n ="level3 4"></l3><br /><l3 n ="level3 5"></l3><br /><l3 n ="level3 6"></l3><br /></l2><br /></l1><br /></p1><br /></root><br /><br /><br />This is my XML.<br />i want result like this<br /><br />P1(n) l1(n)l2(n)l3(n)<br />ALevel1 1Level2 1level3 1<br />ALevel1 1Level2 1level3 2<br />ALevel1 1Level2 1level3 3<br />ALevel1 2Level2 2level3 4<br />ALevel1 2Level2 2level3 5<br />ALevel1 2Level2 2level3 6<br /><br />this is my sql script<br />SELECT *<br />FROM OPENXML(@idoc, ‘root/p1/l1/l2/l3′,1)<br />WITH (p1_nnvarchar(15)’/root/p1/@n’, <br />l1_nint’/root/p1/l1/@n’, <br />l2_nnvarchar (15) ‘/root/p1/l1/@n’, <br />l3_nnvarchar(100)’@n’,<br /><br />)XMLDEG <br /><br /><br />my current result<br />P1(n)l1(n)l2(n)l3(n)<br />ALevel1 1Level2 1level3 1<br />ALevel1 1Level2 1level3 2<br />ALevel1 1Level2 1level3 3<br />ALevel1 1Level2 1level3 4<br />ALevel1 1Level2 1level3 5<br />ALevel1 1Level2 1level3 6<br /><br />Please help<br /><br /><br />Thanks<br />S. RameshDECLARE @idoc int<br />DECLARE @doc varchar(1000)<br />set @doc=’<root><br /><<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />1 n="A"><br /> <l1 n="level1 1"><br /><l2 n="Level2 1"><br /><l3 n ="level3 1"/><br /><l3 n ="level3 2"/><br /><l3 n ="level3 3"/><br /></l2><br /> </l1><br /> <l1 n="level1 2"><br /><l2 n="Level2 2"><br /><l3 n ="level3 4"/><br /><l3 n ="level3 5"/><br /><l3 n ="level3 6"/><br /></l2><br /> </l1><br /></p1><br /></root>'<br /><br />EXEC sp_xml_preparedocument @idoc OUTPUT, @doc<br /><br />SELECT *<br />FROM OPENXML(@idoc, ‘root/p1/l1/l2/l3’,1)<br />WITH (p1_n nvarchar(15) ‘/root/p1/@n’,<br />l1_n nvarchar(15) ‘../@n’,<br />l2_n nvarchar(15) ‘../@n’,<br />l3_n nvarchar(15) ‘@n'<br />)
hi rosh80,<br />your post is incorrect.<br />the correct solution is given below<br /><br /><br />DECLARE @idoc int<br />DECLARE @doc varchar(1000)<br />set @doc=’<root><br /><<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />1 n="A"><br /> <l1 n="level1 1"><br /><l2 n="Level2 1"><br /><l3 n ="level3 1"/><br /><l3 n ="level3 2"/><br /><l3 n ="level3 3"/><br /></l2><br /> </l1><br /> <l1 n="level1 2"><br /><l2 n="Level2 2"><br /><l3 n ="level3 4"/><br /><l3 n ="level3 5"/><br /><l3 n ="level3 6"/><br /></l2><br /> </l1><br /></p1><br /></root>'<br /><br />EXEC sp_xml_preparedocument @idoc OUTPUT, @doc<br /><br />SELECT *<br />FROM OPENXML(@idoc, ‘root/p1/l1/l2/l3’,1) <br />WITH (p1_n nvarchar(15) ‘/root/p1/@n’, <br />l1_n nvarchar(15) ‘../../@n’, <br />l2_n nvarchar(15) ‘../@n’, <br />l3_n nvarchar(15) ‘@n'<br />)<br /><br /><br /><br />Thanks<br />S. Ramesh
Cut copy paste mistake <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />
Btw ur original post had quite a few errors with the sql script and the xml doc.
]]>