DECLARE @doc int
/* Load XML Document (parse) */
BEGIN CALLOUT A
exec sp_xml_preparedocument @doc OUTPUT, '
<Orders>
  <Order>
    <Date>1996-07-04T00:00:00</Date>
    <CustomerRef>VINET</CustomerRef>
    <ShipDate>1996-07-16T00:00:00</ShipDate>
    <ShipName>Vins et alcools Chevalier</ShipName>
    <LineItem pid="11">
      <Quantity>12</Quantity>
      <UnitPrice>14.0000</UnitPrice>
    </LineItem>
    <LineItem pid="42">
      <Quantity>10</Quantity>
      <UnitPrice>9.8000</UnitPrice>
    </LineItem>
    <LineItem pid="72">
      <Quantity>5</Quantity>
      <UnitPrice>34.8000</UnitPrice>
    </LineItem>
  </Order>
  <Order>
    <Date>1996-12-12T00:00:00</Date>
    <CustomerRef>LILAS</CustomerRef>
    <ShipDate>1996-12-13T00:00:00</ShipDate>
    <ShipName>LILA-Supermercado</ShipName>
    <LineItem pid="74">
      <Quantity>14</Quantity>
      <UnitPrice>8.0000</UnitPrice>
    </LineItem>
  </Order>
</Orders>'
END CALLOUT A
BEGIN CALLOUT B
/* Retrieve all Order elements and store in the Orders table. */
INSERT INTO Orders (OrderDate, ShippedDate, ShipName, ImportID)
SELECT *
FROM OpenXML(@doc,'//Order')
     WITH (XMLDate     datetime     'Date',
           XMLShipDate datetime     'ShipDate',
           XMLShipName nvarchar(40) 'ShipName',
           XMLId       int          '@mp:id')
END CALLOUT B

BEGIN CALLOUT C
/* Select the LineItems and join with the Orders
   table to obtain the correct OrderNumber. */
INSERT INTO LineItems (OrderNumber, ProductID, UnitPrice, Quantity)
SELECT Orders.OrderNumber, XMLProductID, XMLUnitPrice, XMLQuantity
FROM OpenXML(@doc,'//LineItem')
     WITH (XMLParentID  int      '@mp:parentid',
           XMLProductID int      '@pid',
           XMLQuantity  smallint 'Quantity',
           XMLUnitPrice money    'UnitPrice') AS oxml
JOIN Orders ON oxml.XMLParentID = Orders.ImportID
END CALLOUT C

/* Unload the XML document. */
EXEC sp_xml_removedocument @doc