SQL Server -Open XML with parent child relationship

I have a parent - child XML data. and i want to ins开发者_Python百科ert the data in SQL using open xml. Customers node will go into customer Table. and the corresponding key generated will go in as foreign key relationship into orders table. How to do this?

<Customers  ContactName="Joe" CompanyName="Company1">
  <Orders  OrderDate="2000-08-25T00:00:00"/>
  <Orders  OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers  ContactName="Steve" CompanyName="Company2">
  <Orders  OrderDate="2000-10-03T00:00:00"/>

</Customers>

---

I assume that identity columns CustomerID and OrderID are primary keys in your Customers and Orders tables and that you have CustomerID as FK in Orders. I use CustomerName and ContactName to locate the CustomerID to use in Orders.CustomerID. If there is a chance that the Customer from the XML is already in the Customer table you should exclude those rows from the insert with a where not exists clause.

First is a version that uses a XML variable like @Richard suggests in his comment.

declare @Customers table (CustomerID int identity, ContactName varchar(50), CompanyName varchar(50))
declare @Orders table (OrderID int identity, CustomerID int, OrderDate datetime)

declare @xml as xml = '
<Customers  ContactName="Joe" CompanyName="Company1">
  <Orders  OrderDate="2000-08-25T00:00:00"/>
  <Orders  OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers  ContactName="Steve" CompanyName="Company2">
  <Orders  OrderDate="2000-10-03T00:00:00"/>
</Customers>'

insert into @Customers (ContactName, CompanyName)
select 
  c.value('@ContactName', 'varchar(50)'),
  c.value('@CompanyName', 'varchar(50)')
from @xml.nodes('Customers') as n(c)

;with cteOrders as
(
  select
    o.value('@OrderDate', 'DateTime') as OrderDate,
    o.value('../@ContactName', 'varchar(50)') as ContactName,
    o.value('../@CompanyName', 'varchar(50)') as CompanyName
  from @xml.nodes('Customers/Orders') as n(o)
)
insert into @Orders (CustomerID, OrderDate)
select C.CustomerID, O.OrderDate
from cteOrders as O 
  inner join @Customers as C
    on C.CompanyName = O.CompanyName and
       C.ContactName = O.ContactName

The second version uses openxml. If you use openxml there can only be one root element so I added a root element to your sample XML.

declare @Customers table (CustomerID int identity, ContactName varchar(50), CompanyName varchar(50))
declare @Orders table (OrderID int identity, CustomerID int, OrderDate datetime)

declare @xml as xml = '
<root>
  <Customers  ContactName="Joe" CompanyName="Company1">
    <Orders  OrderDate="2000-08-25T00:00:00"/>
    <Orders  OrderDate="2000-10-03T00:00:00"/>
  </Customers>
  <Customers  ContactName="Steve" CompanyName="Company2">
    <Orders  OrderDate="2000-10-03T00:00:00"/>
  </Customers>
</root>'

declare @idoc int
exec sp_xml_preparedocument @idoc out, @XML


insert into @Customers (ContactName, CompanyName)
select C.ContactName, C.CompanyName
from openxml(@idoc, '/root/Customers', 0) with
                    (ContactName varchar(50),
                     CompanyName varchar(50)) as C


insert into @Orders (CustomerID, OrderDate)
select C.CustomerID, OrderDate
from openxml(@idoc, '/root/Customers/Orders', 0) with
          (OrderDate datetime,
             ContactName varchar(50) '../@ContactName',
             CompanyName varchar(50) '../@CompanyName') as O
  inner join @Customers as C
    on C.CompanyName = O.CompanyName and
       C.ContactName = O.ContactName

exec sp_xml_removedocument @idoc