CODEWITHSUNDEEP
xmlt-sqlparsing
Cristen Rafalko
Cristen Rafalko

Reputation: 85

T-SQL - Parse Item Child is XML in Table

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.

Upvotes: 1

Views: 34

Answers (1)

marc_s
marc_s

Reputation: 755157

Try this:

INSERT INTO @Result(ItemId, Quantity, ItemChild, QtyChild)
    SELECT
        t.ItemId,
        t.Quantity,
        xc.value('(item/@internalId)[1]', 'NVARCHAR(250)'),
        xc.value('(quantity)[1]', 'NVARCHAR(250)')
    FROM
        @tbl t
    CROSS APPLY
        t.xmlData.nodes('/record/memberList/itemMember') AS XT(XC)

But I'd also recommend using the most appropriate data types for your columns ItemChild and QtyChild - those are numeric, so you should really not store them as NVARCHAR(250) - use INT for ItemChild, and DECIMAL(18,2) for QtyChild instead!

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

INSERT INTO @Result(ItemId, Quantity, ItemChild, QtyChild)
    SELECT
        t.ItemId,
        t.Quantity,
        xc.value('(item/@internalId)[1]', 'INT'),
        xc.value('(quantity)[1]', 'DECIMAL(18, 2)')
    FROM
        @tbl t
    CROSS APPLY
        t.xmlData.nodes('/record/memberList/itemMember') AS XT(XC)

Upvotes: 1

Related Questions