Home T-SQL - Parse Item Child is XML in Table
Reply: 0

T-SQL - Parse Item Child is XML in Table

user1548
1#
user1548 Published in May 25, 2018, 11:08 am

I have this table

DECLARE @tbl TABLE 
             (
                [ItemId] NVARCHAR(250),
                [Quantity] DECIMAL(18, 2),
                xmlData XML
             );

INSERT INTO @tbl 
VALUES ('Item1', 3, '<record>  <memberList>    <itemMember>      <quantity>3.0</quantity>      <item internalId="553">             </item>         </itemMember>    <itemMember>      <quantity>2.0</quantity>      <item internalId="554">         </item>       </itemMember> </memberList></record>'),
       ('Item2', 5, '<record>  <memberList>    <itemMember>      <quantity>4.0</quantity>      <item internalId="53">           </item>        </itemMember>    <itemMember>      <quantity>6.0</quantity>      <item internalId="54">             </item>         </itemMember>    </memberList></record>')

I try to parse the XML to get Item Child in table.

My expected result:

DECLARE @Result TABLE 
                (
                    [ItemId] NVARCHAR(250),
                    [Quantity] DECIMAL(18, 2),
                    [ItemChild] NVARCHAR(250),
                    [QtyChild] NVARCHAR(250)
                );

INSERT INTO @tbl 
VALUES ('Item1', 3, '553', 3), ('Item1', 3, '554', 2),
       ('Item2', 5, '53', 4), ('Item2', 5, '54', 6)

Please help me. Thanks.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.479283 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO