Discussion in 'SQL Server 2005 General Developer Questions' started by arkiboys, Jan 27, 2009.
Using SQL, how is it possible to read the contents of an xml file please?
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
@customers = document FROM #tbl_XMLDataSELECT
nCol.value('customer_id', 'integer') as cust_id,nCol
.value('customer_name', 'varchar(32)') as cust_nameFROM @customers.nodes('/Customers/Customer') AS nTable(nCol)
Here's the sample XML file
Separate names with a comma.