Here is a brief example of importing an XML file into SQL Server table. This is accomplished by using the BULK option of OPENROWSET to load the file, and then utilizing the XQuery capabilities of SQL Server to parse the XML to normalized table format. This example requires SQL server 2005 or SQL Server 2008.
First, the following XML is saved to XML file C:\Products.xml.
<Products>
<Product>
<SKU>1</SKU>
<Desc>Book</Desc>
</Product>
<Product>
<SKU>2</SKU>
<Desc>DVD</Desc>
</Product>
<Product>
<SKU>3</SKU>
<Desc>Video</Desc>
</Product>
</Products>
Next, a table named Products is created to store the XML data.
CREATE TABLE Products( sku INT PRIMARY KEY, product_desc VARCHAR(30));
Finally, the following statement will load the XML file, parse the XML elements to columns, and insert into the Products table:
INSERT INTO Products (sku, product_desc)
SELECT X.product.query('SKU').value('.', 'INT'),
X.product.query('Desc').value('.', 'VARCHAR(30)')
FROM ( SELECT CAST(x AS XML)
FROM OPENROWSET(
BULK 'C:\Projects\MM\Projects\JobFeedXML',
SINGLE_BLOB) AS T(x)
) AS T(x) CROSS APPLY x.nodes('Products/Product') AS X(product);
bulk_column_alias
Is an optional alias to replace a column name in the result set. Column aliases are allowed only in SELECT statements that use the OPENROWSET function with the BULK option. When you use bulk_column_alias, specify an alias for every table column in the same order as the columns in the file.
Source: http://msdn.microsoft.com/en-us/library/ms177634.aspx
Source: http://pratchev.blogspot.com/2008/11/import-xml-file-to-sql-table.html
Source: http://msdn.microsoft.com/en-us/library/ms191184.aspx#existing_row
Subscribe