Loop XML data and insert into another table using SQL stored procedure

Question

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.

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

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