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

Share Improve this question Follow edited Nov 18, 2024 at 12:56 marc_s 757k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Nov 18, 2024 at 10:17 ArunArun 2733 bronze badges 1

• 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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