question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked ErlandSommarskog commented

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

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered ErlandSommarskog commented

Hi @TZacks-2728,

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;


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


In the above merge how could i update data in state table if there is stateID in state table ?
i want to have a code example which will insert data into state table and update if stateID is there in xml. whatever happen stateid will be stored in @idmapping table.

;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;

same way i want to use merge statement to insert or update data into city table. how it will be possible.
can you please post a another sample code where merge statement will be used to insert/update data into state & city table.

Thanks

0 Votes 0 ·

< In the above merge how could i update data in state table if there is stateID in state table ?

You were asking about how to insert data, not to update, so Yithzak gave you an example for INSERT.

The reason he is using MERGE is that with INSERT you cannot get the mapping between IDENTITY value and the source data, but MERGE permits this.

If you want help with a different problem I suggest that you start a new thread, including CREATE TABLE, INSERT and the appropriate XML.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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://docs.microsoft.com/en-us/answers/questions/601599/sql-server-how-to-show-master-amp-detail-data-from.html

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ErlandSommarskog commented

Hi @TZacks-2728,

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

in my xml there is OrderID which has value but suppose OrderID is identity type in table then how could i insert data into ordertable and store orderid of all newly inserted data in some where because when i will insert data into OrderDetail table then i need to store orderid in this table.

how to achieve it. think in my xml orderid has a value which is 0. now please guide how to store data into master & detail table from xml. thanks

0 Votes 0 ·

in my xml there is OrderID which has value but suppose OrderID is identity type in table then how could i insert data into ordertable.

Sorry, I don't quite understand what you mean, can you explain it in detail? It is recommended that you provide a minimal example that can be tested and your expected output from that example.

0 Votes 0 ·

in my xml there is OrderID which has value but suppose OrderID is identity type in table then how could i insert data into ordertable

If the XML you are reading has an OrderID, and you have made that column an IDENTITY column in your table, you need to start over with your tables. IDENTITY is for cases when you generate the keys yourself - and you have a insertion frequency that is high enough to rule out rolling your own. It is a highly over-used feature, and in this context it is plain wrong.

Now, if the XML document has does not have any usable key, because you need to generate it yourself, you can to like Yithzak do below. (But I would still not use IDENTITY, unless there really are througput requirements that calls for it.)

0 Votes 0 ·
TZacks-2728 avatar image
0 Votes"
TZacks-2728 answered
 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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.