how do i query and XML file

• sukhoi971

  SSCarpal Tunnel

  Points: 4833

  More actions

  March 1, 2004 at 10:55 pm

  #161039

  select * from based on some criteria and bound it to a recordset

  <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

  *Sukhoi*[font="Arial Narrow"][/font]

• Dinesh Asanka

  SSChampion

  Points: 11058

  More actions

  March 1, 2004 at 11:13 pm

  #496585

  Use OPENXML

   

  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)

              WITH (CustomerID  varchar(10),

                    ContactName varchar(20))

  
  
  
  

  My Blog: http://dineshasanka.spaces.live.com/