How to Import data from XML type column and insert them into another table?

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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.

HTML

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

SQL

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

Posted 15-May-14 18:59pm

LEBON Iniyavan

Updated 15-May-14 19:55pm

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ravi Shankar Dokka · Answer 1 · 2014-05-20T04:44:00

Hi,

Please try using the following way:

--Creating a temporary table variable

declare @Temp table
(
  XMLCol xml
)

--insert your xml data

insert into @Temp values
('<your xml data>')

--selecting the records from the xml column
select X.N.value(N'(sku)[1]', 'nvarchar(max)') as _sku,
       X.N.value(N'(desc)[1]', 'nvarchar(max)') as _desc,
       X.N.value(N'(test)[1]', 'nvarchar(max)') as _test
from @Temp as T
  cross apply T.XMLCol.nodes(N'/products/product') as X(N)