SQL Server Performance

How to read top level values in xml

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by sramesh, Jul 26, 2006.

  1. sramesh New Member

    Hi all,<br />&lt;root&gt;<br />&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />1 n="A"&gt;<br />&lt;l1 n="level1 1&gt;<br />&lt;l2 n="Level2 1&gt;<br />&lt;l3 n ="level3 1"&gt;&lt;/l3&gt;<br />&lt;l3 n ="level3 2"&gt;&lt;/l3&gt;<br />&lt;l3 n ="level3 3"&gt;&lt;/l3&gt;<br />&lt;/l2&gt;<br />&lt;/l1&gt;<br />&lt;l1 n="level1 2&gt;<br />&lt;l2 n="Level2 2&gt;<br />&lt;l3 n ="level3 4"&gt;&lt;/l3&gt;<br />&lt;l3 n ="level3 5"&gt;&lt;/l3&gt;<br />&lt;l3 n ="level3 6"&gt;&lt;/l3&gt;<br />&lt;/l2&gt;<br />&lt;/l1&gt;<br />&lt;/p1&gt;<br />&lt;/root&gt;<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. Ramesh
  2. rosh80 New Member

    DECLARE @idoc int<br />DECLARE @doc varchar(1000)<br />set @doc='&lt;root&gt;<br />&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />1 n="A"&gt;<br /> &lt;l1 n="level1 1"&gt;<br />&lt;l2 n="Level2 1"&gt;<br />&lt;l3 n ="level3 1"/&gt;<br />&lt;l3 n ="level3 2"/&gt;<br />&lt;l3 n ="level3 3"/&gt;<br />&lt;/l2&gt;<br /> &lt;/l1&gt;<br /> &lt;l1 n="level1 2"&gt;<br />&lt;l2 n="Level2 2"&gt;<br />&lt;l3 n ="level3 4"/&gt;<br />&lt;l3 n ="level3 5"/&gt;<br />&lt;l3 n ="level3 6"/&gt;<br />&lt;/l2&gt;<br /> &lt;/l1&gt;<br />&lt;/p1&gt;<br />&lt;/root&gt;'<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 />)
  3. sramesh New Member

    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='&lt;root&gt;<br />&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />1 n="A"&gt;<br /> &lt;l1 n="level1 1"&gt;<br />&lt;l2 n="Level2 1"&gt;<br />&lt;l3 n ="level3 1"/&gt;<br />&lt;l3 n ="level3 2"/&gt;<br />&lt;l3 n ="level3 3"/&gt;<br />&lt;/l2&gt;<br /> &lt;/l1&gt;<br /> &lt;l1 n="level1 2"&gt;<br />&lt;l2 n="Level2 2"&gt;<br />&lt;l3 n ="level3 4"/&gt;<br />&lt;l3 n ="level3 5"/&gt;<br />&lt;l3 n ="level3 6"/&gt;<br />&lt;/l2&gt;<br /> &lt;/l1&gt;<br />&lt;/p1&gt;<br />&lt;/root&gt;'<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
  4. rosh80 New Member

    Cut copy paste mistake <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  5. rosh80 New Member

    Btw ur original post had quite a few errors with the sql script and the xml doc.

Share This Page