I have this XML:

<Root>
    <NEW_BATCH_NUM>A1234</NEW_BATCH_NUM>
    <NEW_LOCATION>US11</NEW_LOCATION>
    <TRANSACTION_DATE>11/8/2024 6:54:03 AM</TRANSACTION_DATE>
    <CHILD_BATCHES>
        <CHILDBATCH>
            <BATCH_NUM>C101</BATCH_NUM>
            <QTY>1500</QTY>
        </CHILDBATCH>
        <CHILDBATCH>
            <BATCH_NUM>C102</BATCH_NUM>
            <QTY>2000</QTY>
        </CHILDBATCH>
    </CHILD_BATCHES>
</Root>

Requirement is based on the CHILDBATCH node, I need to insert data into another table using a SQL stored procedure.

Expected result is

NEW_BATCH_NUM   NEW_LOCATION    CHILD_BATCH_NUM   QTY
------------------------------------------------------
A1234               US11            C101         1500
A1234               US11            C102         2000 

Please guide here.

I have this XML:

<Root>
    <NEW_BATCH_NUM>A1234</NEW_BATCH_NUM>
    <NEW_LOCATION>US11</NEW_LOCATION>
    <TRANSACTION_DATE>11/8/2024 6:54:03 AM</TRANSACTION_DATE>
    <CHILD_BATCHES>
        <CHILDBATCH>
            <BATCH_NUM>C101</BATCH_NUM>
            <QTY>1500</QTY>
        </CHILDBATCH>
        <CHILDBATCH>
            <BATCH_NUM>C102</BATCH_NUM>
            <QTY>2000</QTY>
        </CHILDBATCH>
    </CHILD_BATCHES>
</Root>

Requirement is based on the CHILDBATCH node, I need to insert data into another table using a SQL stored procedure.

Expected result is

NEW_BATCH_NUM   NEW_LOCATION    CHILD_BATCH_NUM   QTY
------------------------------------------------------
A1234               US11            C101         1500
A1234               US11            C102         2000 

Please guide here.

• xml
• stored-procedures

• 3 While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Commented Nov 18, 2024 at 12:49

1 Answer 1

There is no need to loop for such tasks. It could be done as a set based operation.

XQuery .nodes() method is converting XML into rectangular/relational format.

CROSS APPLY is simulating one-to-many relationship.

SQL

DECLARE @parameter XML =
N'<Root>
    <NEW_BATCH_NUM>A1234</NEW_BATCH_NUM>
    <NEW_LOCATION>US11</NEW_LOCATION>
    <TRANSACTION_DATE>11/8/2024 6:54:03 AM</TRANSACTION_DATE>
    <CHILD_BATCHES>
        <CHILDBATCH>
            <BATCH_NUM>C101</BATCH_NUM>
            <QTY>1500</QTY>
        </CHILDBATCH>
        <CHILDBATCH>
            <BATCH_NUM>C102</BATCH_NUM>
            <QTY>2000</QTY>
        </CHILDBATCH>
    </CHILD_BATCHES>
</Root>';

-- INSERT INTO targetTable -- uncomment when you are ready
SELECT NEW_BATCH_NUM = p.value('(NEW_BATCH_NUM/text())[1]', 'VARCHAR(30)')
    , NEW_LOCATION = p.value('(NEW_LOCATION/text())[1]', 'VARCHAR(30)')
    , CHILD_BATCH_NUM  = c.value('(BATCH_NUM/text())[1]', 'VARCHAR(30)')
    , QTY  = c.value('(QTY/text())[1]', 'INT')
FROM @parameter.nodes('/Root') AS t1(p)   -- parent
CROSS APPLY t1.p.nodes('CHILD_BATCHES/CHILDBATCH') AS t2(c); -- child

Output