how to insert data from xml document into sql server table?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Dear Friends...

i have a xml document and i want to load data from that xml document into new table created based on that document. That's why i have written following query but it throws an error

SQL

 select * into t from
(SELECT 10, xCol
FROM    (SELECT *
    FROM OPENROWSET (BULK 'C:\Users\TAUSEEF\Desktop\test.xml', SINGLE_CLOB)
 AS xCol) AS R(xCol))

Error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

Please help me

Posted 26-Aug-15 0:23am

TAUSEEF KALDANE

Add a Solution

2 solutions

Solution 1

Try to use this,

SQL

select * into t from
(SELECT 10, xCol
FROM    (SELECT *
    FROM OPENROWSET (BULK 'C:\Users\TAUSEEF\Desktop\test.xml', SINGLE_CLOB)
 AS xCol) AS R(xCol)) a

Posted 26-Aug-15 0:50am

deepankarbhatnagar

Comments

TAUSEEF KALDANE 26-Aug-15 7:05am

No Sir, i tried ur query and it is also throwing errors as follows
1. Msg 8155, Level 16, State 2, Line 5
No column name was specified for column 1 of 'a'.

2. Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

deepankarbhatnagar 26-Aug-15 7:11am

OK let me check again.

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)

Advay Pandya · Accepted Answer · 2015-08-26T01:23:00

Hi TAUSEEF,

In addition to the answer of deepankarbhatnagar,

Please try with below query. In the inner query, column name should be there for the column value 10.

SQL

select * into t from
(SELECT 10 as Col1, xCol
FROM    (SELECT *
    FROM OPENROWSET (BULK 'C:\Users\TAUSEEF\Desktop\test.xml', SINGLE_CLOB)
 AS xCol) AS R(xCol)) a

Please let me know if you have any concern or query on this or if you are facing any other issue.

Thanks