OPen xml

• Jules Bonnot

  SSCertifiable

  Points: 5488

  More actions

  November 15, 2006 at 10:38 am

  #67808

  I need to insert the results from an open xml query into a table. However i only want to selct the first parent node and its child not all parents nodes. Is there a way i can do this with openxml queries or wil i have to do something else.

  www.sql-library.com[/url]

• peterhe

  SSChampion

  Points: 11469

  More actions

  November 16, 2006 at 7:05 am

  #672258

  Yes, you can do this by OPENXML. The following is a sample to select just the first Customer's order:

  DECLARE @idoc int

  DECLARE @doc varchar(1000)

  SET @doc ='

  <ROOT>

  <Customer CustomerID="VINET" ContactName="Paul Henriot">

     <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">

        <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>

        <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>

     </Order>

  </Customer>

  <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

     <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">

        <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>

     </Order>

  </Customer>

  </ROOT>'

  --Create an internal representation of the XML document.

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

  -- Execute a SELECT statement that uses the OPENXML rowset provider.

  SELECT *

  FROM   OPENXML (@idoc, '/ROOT/Customer[1]/Order/OrderDetail',2)

           WITH (OrderID       int         '@OrderID',

                 CustomerID  varchar(10) '../@CustomerID',

                 CustomerName  varchar(20) '../../@ContactName',

                 OrderDate   datetime    '../@OrderDate',

                 ProdID      int         '@ProductID',

                 Qty         int         '@Quantity')