DECLARE @doc int
/* Load the XML document (parse). */
BEGIN CALLOUT A
exec sp_xml_preparedocument @doc OUTPUT, '
<Products>
  <Product name="Chai" weight="2">
    <QuantityPerUnit>10 boxes x 20 bags</QuantityPerUnit>
    <UnitPrice>18.0000</UnitPrice>
  </Product>
  <Product name="Chang" weight="20">
    <QuantityPerUnit>24 - 12 oz bottles</QuantityPerUnit>
    <UnitPrice>19.0000</UnitPrice>
  </Product>
  <Product name="Aniseed Syrup" weight="10">
    <QuantityPerUnit>12 - 550 ml bottles</QuantityPerUnit>
    <UnitPrice>10.0000</UnitPrice>
  </Product>
  <Product name="Chef Anton's Cajun Seasoning" weight="28">
    <QuantityPerUnit>48 - 6 oz jars</QuantityPerUnit>
    <UnitPrice>22.0000</UnitPrice>
  </Product>
  <Product name="Chef Anton's Gumbo Mix" weight="9">
    <QuantityPerUnit>36 boxes</QuantityPerUnit>
    <UnitPrice>21.3500</UnitPrice>
  </Product>
</Products>'
END CALLOUT A
/* Shred the XML and capture the "overflow." */
BEGIN CALLOUT B
INSERT INTO Products(ProductName, UnitPrice, Overflow)
SELECT XMLProductName, XMLUnitPrice, XMLOverflow
FROM OpenXML(@doc,'//Product',8)
     WITH (XMLProductName nvarchar(40) '@name',
           XMLUnitPrice   money        'UnitPrice',
           XMLOverflow    text         '@mp:xmltext')
END CALLOUT B
/* Unload the XML document. */
exec sp_xml_removedocument @doc