Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    29

    Question Unanswered: How can i read an xml file from SQLServer

    I need to know how to read an xml file using SQL. (in SQLServer)
    Then i will manipulate the data and update my tables.

    May be i can put the xml file in the IIS root.
    Then what is required for the rest..

    Thanks in advance

    Benny
    Last edited by bennydubai; 03-31-04 at 03:34.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: How can i read an xml file from SQLServer

    Originally posted by bennydubai
    I need to know how to read an xml file using SQL. (in SQLServer)
    Then i will manipulate the data and update my tables.

    May be i can put the xml file in the IIS root.
    Then what is required for the rest..

    Thanks in advance

    Benny
    Check openXML and other XML related topics in BOL,

    Here is a sample from BOL:


    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))

  3. #3
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    29
    I understood what u meant in OPENXML.
    But how do we physically read from an XML file in that case

    Thanks in advance

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Originally posted by bennydubai
    I understood what u meant in OPENXML.
    But how do we physically read from an XML file in that case

    Thanks in advance

    i have not worked on it, but u can pass the xml as a parameter to the stored proc or dts it in a table.
    i think after u dts the raw xml in a table u can read and manipulate it easily.

  5. #5
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    29
    I think the only way to read from XML file to the database is by using vbscript and calling some SQLXMLBulkLoad commands..
    Let me know if there are better ways please...

    And how do we write an xml ( that we get in the for xml clause) in to a file?

    Please help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •