SQL Server How to insert / update master-detail data into master & detail tables from xml

Question

This is a sample master details data in xml. please advise me how to insert these data into two table. one is Order and details is Order_Detail table

DECLARE @x xml = 
'<Orders>
    <Order>
       <OrderID>13000</OrderID>
       <CustomerID>ALFKI</CustomerID>
       <OrderDate>2006-09-20Z</OrderDate>
       <EmployeeID>2</EmployeeID>
       <OrderDetails>
           <ProductID>76</ProductID>
           <Price>123</Price>
           <Qty>10</Qty>
       </OrderDetails>
       <OrderDetails>
           <ProductID>16</ProductID>
           <Price>3.23</Price>
           <Qty>20</Qty>
       </OrderDetails>
    </Order>
    <Order>
       <OrderID>13001</OrderID>
       <CustomerID>VINET</CustomerID>
       <OrderDate>2006-09-20Z</OrderDate>
       <EmployeeID>1</EmployeeID>
       <OrderDetails>
           <ProductID>12</ProductID>
           <Price>12.23</Price>
           <Qty>1</Qty>
       </OrderDetails>
    </Order>
</Orders>'

i can loop into the xml data and insert one records at a time then i can store orderid of each inserted records and store that orderid into order_detail table for reference.

but if i want to insert or update the data from xml using Merge then how could i orderid into variable which will be inserted or updated into order_Details table.

please discuss few approach to handle this scenario. thanks

Answer 1

Hi @T.Zacks ,

Please try the following conceptual example.
It shows how to handle parent/child tables, hierarchical XML, and identities.

SQL

-- DDL and sample data population, start  
USE tempdb;  
GO  
  
DROP TABLE IF EXISTS #city;  
DROP TABLE IF EXISTS #state;  
  
-- parent table  
CREATE TABLE #state  (  
   stateID INT IDENTITY PRIMARY KEY,   
   stateName VARCHAR(30),   
   abbr CHAR(2),   
   capital VARCHAR(30)  
);  
-- child table (1-to-many)  
CREATE TABLE #city (  
   cityID INT IDENTITY,   
   stateID INT NOT NULL FOREIGN KEY REFERENCES #state(stateID),   
   city VARCHAR(30),   
   [population] INT,  
   PRIMARY KEY (cityID, stateID, city)  
);  
-- mapping table to preserve IDENTITY ids  
DECLARE @idmapping TABLE (GeneratedID INT PRIMARY KEY,  
    NaturalID VARCHAR(20) NOT NULL UNIQUE);  
  
DECLARE @xml XML =  
N'<root>  
   <state>  
      <StateName>Florida</StateName>  
      <Abbr>FL</Abbr>  
      <Capital>Tallahassee</Capital>  
      <cities>  
         <city>  
            <city>Miami</city>  
            <population>470194</population>  
         </city>  
         <city>  
            <city>Orlando</city>  
            <population>285713</population>  
         </city>  
      </cities>  
   </state>  
   <state>  
      <StateName>Texas</StateName>  
      <Abbr>TX</Abbr>  
      <Capital>Austin</Capital>  
      <cities>  
         <city>  
            <city>Houston</city>  
            <population>2100263</population>  
         </city>  
         <city>  
            <city>Dallas</city>  
            <population>5560892</population>  
         </city>  
      </cities>  
   </state>  
</root>';  
-- DDL and sample data population, end  
  
;WITH rs AS   
(  
    SELECT stateName   = p.value('(StateName/text())[1]', 'VARCHAR(30)'),  
           abbr         = p.value('(Abbr/text())[1]', 'CHAR(2)'),  
           capital      = p.value('(Capital/text())[1]', 'VARCHAR(30)')  
    FROM   @xml.nodes('/root/state') AS t(p)  
 )  
 MERGE #state AS o  
 USING rs ON 1 = 0  
 WHEN NOT MATCHED THEN  
    INSERT(stateName, abbr, capital)    
       VALUES(rs.stateName, rs.Abbr, rs.Capital)  
 OUTPUT inserted.stateID, rs.stateName   
   INTO @idmapping (GeneratedID, NaturalID);  
  
;WITH Details AS   
(  
    SELECT NaturalID   = p.value('(StateName/text())[1]', 'VARCHAR(30)'),  
           city         = c.value('(city/text())[1]', 'VARCHAR(30)'),  
           [population]   = c.value('(population/text())[1]', 'INT')  
    FROM   @xml.nodes('/root/state') AS A(p)   -- parent  
      CROSS APPLY A.p.nodes('cities/city') AS B(c) -- child  
)   
INSERT #city (stateID, city, [Population])  
SELECT m.GeneratedID, d.city, d.[Population]  
FROM   Details AS d  
   INNER JOIN @idmapping AS m ON d.NaturalID = m.NaturalID;  
  
-- test  
SELECT * FROM #state;  
SELECT * FROM @idmapping;  
SELECT * FROM #city;  

Answer 2

You just learnt(*) to write the SELECT statements to get the data. So now you only need to add INSERT before the SELECT. That's not any different, just because the data comes from XML.

(*) For those who did not see it, the above XML comes from this thread: https://learn.microsoft.com/en-us/answers/questions/601599/sql-server-how-to-show-master-amp-detail-data-from.html

Answer 3

Hi @T.Zacks ,

After getting the data from XML, you can use the ordinary INSERT statement to insert into the target table. Based on the Erland statement in your previous post, use the INSERT statement to insert as follows:

    CREATE TABLE [Order](OrderID int,CustomerID nchar(5),  
    OrderDate datetime,EmployeeId smallint)  
      
    CREATE TABLE Order_Detail(OrderID int,ProductID int,  
           Price decimal(10,2),Qty int)  
      
    DECLARE @x xml =   
    '<Orders>  
        <Order>  
           <OrderID>13000</OrderID>  
           <CustomerID>ALFKI</CustomerID>  
           <OrderDate>2006-09-20Z</OrderDate>  
           <EmployeeID>2</EmployeeID>  
           <OrderDetails>  
               <ProductID>76</ProductID>  
               <Price>123</Price>  
               <Qty>10</Qty>  
           </OrderDetails>  
           <OrderDetails>  
               <ProductID>16</ProductID>  
               <Price>3.23</Price>  
               <Qty>20</Qty>  
           </OrderDetails>  
        </Order>  
        <Order>  
           <OrderID>13001</OrderID>  
           <CustomerID>VINET</CustomerID>  
           <OrderDate>2006-09-20Z</OrderDate>  
           <EmployeeID>1</EmployeeID>  
           <OrderDetails>  
               <ProductID>12</ProductID>  
               <Price>12.23</Price>  
               <Qty>1</Qty>  
           </OrderDetails>  
        </Order>  
    </Orders>'  
      
    INSERT INTO [Order]  
    SELECT OrderID    = T.Item.value('(OrderID/text())[1]', 'int'),  
           CustomerID = T.Item.value('(CustomerID/text())[1]', 'nchar(5)'),  
           OrderDate  = T.Item.value('(OrderDate/text())[1]',  'datetime'),  
           EmployeeId = T.Item.value('(EmployeeID/text())[1]', 'smallint')  
    FROM   @x.nodes('Orders/Order') AS T(Item)  
      
    INSERT INTO Order_Detail  
    SELECT OrderID    = O.n.value('(OrderID/text())[1]',   'int'),  
           ProductID  = D.n.value('(ProductID/text())[1]', 'int'),  
           Price      = D.n.value('(Price/text())[1]',     'decimal(10,2)'),  
           Qty        = D.n.value('(Qty/text())[1]',       'int')  
    FROM   @x.nodes('/Orders/Order') AS O(n)  
    CROSS APPLY O.n.nodes('OrderDetails') AS D(n)  
      
    SELECT * FROM [Order]  
    SELECT * FROM Order_Detail  
      
    DROP TABLE [Order]  
    DROP TABLE Order_Detail  

Regards
Echo

Answer 4

This is another approach where single order and multiple detail is there. here is the code.

    <?xml version="1.0"?>
    <Order>
        <CustomerID>ALFKI</CustomerID>
        <EmployeeID>3</EmployeeID>
        <OrderDate>07/3/2004</OrderDate>
        <RequiredDate>07/4/2004</RequiredDate>
        <ShippedDate>15/3/2004</ShippedDate>
        <OrderDetails ProductID="2" UnitPrice="15" Quantity="5" Discount="0.15"></OrderDetails>
        <OrderDetails ProductID="4" UnitPrice="22" Quantity="7" Discount="0.21"></OrderDetails>
        <OrderDetails ProductID="10" UnitPrice="31" Quantity="3" Discount="0.15"></OrderDetails>
    </Order>

    CREATE PROCEDURE xmlOrderInsert 
    (
        @order ntext 
    )
    AS

    DECLARE @docHandle int, @OID int

    EXEC sp_xml_preparedocument @docHandle OUTPUT, @order

    BEGIN TRANSACTION

        INSERT INTO Orders( CustomerID, EmployeeID, OrderDate, RequiredDate ) 
        SELECT CustomerID, EmployeeID, OrderDate, RequiredDate 
        FROM Openxml( @docHandle, '/Order', 3) 
        WITH 
        ( 
            CustomerID nchar(5), 
            EmployeeID int,   
            OrderDate datetime, 
            RequiredDate datetime   
        )

        IF @@ERROR<>0 
        BEGIN 
            ROLLBACK TRANSACTION 
            RETURN -100 
        END

        SET @OID = SCOPE_IDENTITY()

        INSERT INTO [Order Details] ( OrderID, ProductID, UnitPrice, Quantity, Discount ) 
        SELECT @OID AS [PO ID], ProductID, UnitPrice, Quantity, Discount 
        FROM OpenXml( @docHandle, '/Order/OrderDetails', 1)   
        WITH 
        ( 
            ProductID int, 
            UnitPrice money, 
            Quantity smallint, 
            Discount real   
        ) 

        IF @@ERROR<>0 
        BEGIN 
            ROLLBACK TRANSACTION RETURN -101 
        END

        COMMIT TRANSACTION
        EXEC sp_xml_removedocument @docHandle SELECT @OID AS [Order ID]
    GO