SQL Server Performance

Read xml file

Discussion in 'SQL Server 2005 General Developer Questions' started by arkiboys, Jan 27, 2009.

  1. arkiboys Member

    Hello,
    Using SQL, how is it possible to read the contents of an xml file please?
    Thanks
  2. greenant New Member

    Hi,
    One way to do this is to use OPENROWSET to read the contents of the XML file into a table, You can then assign the character string to an xml type variable. You can use the nodes method of the xml data type to retrieve data from the xml document.CREATE
    TABLE #tbl_XMLData(Document VARCHAR(MAX))GOINSERT INTO #tbl_XMLData(Document) SELECT * FROM OPENROWSET(BULK N'C:customers.xml', SINGLE_CLOB) AS XMLFile
    GODECLARE @customers XML
    SELECT
    @customers = document FROM #tbl_XMLDataSELECT
    nCol.value('customer_id[1]', 'integer') as cust_id,nCol
    .value('customer_name[1]', 'varchar(32)') as cust_nameFROM @customers.nodes('/Customers/Customer') AS nTable(nCol)
    Here's the sample XML file
    <Customers>
    <Customer>
    <customer_id>1</customer_id>
    <customer_name>John Smith</customer_name>
    </Customer>
    <Customer>
    <customer_id>12</customer_id>
    <customer_name>Jane Doe</customer_name>
    </Customer>
    </Customers>

Share This Page