Hi all,
Here is my scenerio, I have a table which contains a column of type XML DATA type.
Case 1:
Now I need to import the data from that xml file and need to insert them in the respective columns in the same table.
Case 2: I would like to get data from that xml file and have to insert into another table and can make a foregin key relationship between these two tables.
The below is the sample structure I give.
<products>
<product>
<sku>1</sku>
<desc>Book</desc>
<test>pass</test>
</product>
<product>
<sku>2</sku>
<desc>DVD</desc>
<test>pass</test>
</product>
<product>
<sku>3</sku>
<desc>Video</desc>
<test>fail</test>
</product>
</products>
This is the query which am using now, what it does it takes the xml file from the path am giving.. i dont want the path to specify.. i want to take it from the column of xml type. How to do it?
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 'D:\Products.xml',
SINGLE_BLOB) AS T(x)
) AS T(x)
CROSS APPLY x.nodes('Products/Product') AS X(product);
Instead of giving the path, i need to mention the column name. :)
Please provide me some queries that will execute successfully.
Thanks in advance..