inserting xml to sql server

Question

4.00/5 (2 votes)

See more:

Hi all;

I have some problem in insert part of xml as xml

SQL

   <cars>
      <car>
         <Name>Audi</Name>
	 <Color>Red</Color>
	 <Detail>           
	    <a>10</a>
	    <c>50-c</c>
	 </Detail>
      </car>

      <car>
         <Name>KIA</Name>
         <Color>Green</Color>
         <Detail>
            <a>20</a>
            <c>51-c</c>
         </Detail>
      </car>
</cars>

when try to open this xml I used the following code

SQL

create table #tmp (Name nvarchar(100), Color nvarchar(100),Detail nvarchar(100))

INSERT INTO #tmp (Name, Color,Detail)
SELECT 
    	x.y.value( 'Name[1]', 'NVARCHAR(20)' ) AS Name,
    	x.y.value( 'Color[1]', 'NVARCHAR(20)' ) AS Color,
    	x.y.value( 'Detail[1]','NVARCHAR(20)')as Detail
    	
    FROM @x.nodes('cars/car') x(y)

select * from #tmp

The Result is:

SQL

Name        Color        Detail
----        ----         -----
Audi        Red           1050-c
KIA         Green         2051-c

but what I need is:

SQL

Name      Color      Detail
----      ----       -----
Audi      Red        <a>10</a><c>50-c</c>
KIA       Green      <a>20</a><c>51-c</c>

please if any one Know

Thanks ^_^

Posted 2-Dec-12 20:29pm

eng.dzdz

Updated 2-Dec-12 21:28pm

__TR__

v2

Add a Solution

2 solutions

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)

**AmitGajjar** · Accepted Answer · 2012-12-02T21:02:00

Solution 1

Hi,

Instead of your select query use below query and check result,

SQL

SELECT
        x.y.value( 'Name[1]', 'NVARCHAR(20)' ) AS Name,
        x.y.value( 'Color[1]', 'NVARCHAR(20)' ) AS Color,
        x.y.query( 'Detail[1]')as Detail

    FROM @x.nodes('cars/car') x(y)

Although i have not tired this but this should resolve your query.

Best luck.

Posted 2-Dec-12 21:02pm

AmitGajjar

Updated 2-Dec-12 21:22pm

v2

Comments

__TR__ 3-Dec-12 3:23am

Modified your query to get rid of the syntax error in query method.
My 5! I used this solution to post my answer below :)

AmitGajjar 3-Dec-12 3:27am

No issue. Important thing is, OP should get his resolution.

__TR__ · Accepted Answer · 2012-12-02T21:20:00

Solution 2

Try this. It is a slight modification to the query posted by AmitGajjar

SQL

SELECT 
    	x.y.value( 'Name[1]', 'NVARCHAR(20)' ) AS Name,
    	x.y.value( 'Color[1]', 'NVARCHAR(20)' ) AS Color,
    	REPLACE(REPLACE(CAST (x.y.query( 'Detail[1]') AS NVARCHAR(50)), '<detail>',''),'</detail>','')
    FROM @x.nodes('cars/car') x(y)

Hope this helps.

Posted 2-Dec-12 21:20pm

__TR__

Comments

AmitGajjar 3-Dec-12 3:25am

Thanks for the update... actually i have never tried it but it looks good for me. 5+ for you.

__TR__ 3-Dec-12 3:30am

Thanks.

eng.dzdz 3-Dec-12 4:06am

Thank you Very much
this is very helpful

but if I want it without the Detail tag
is it possile

and sorry for bothering

__TR__ 3-Dec-12 4:22am

This query gives the Detail without the Detail Tag. If you observe i have used REPLACE function to remove the detail tags.
Here is the output i got when i ran this query.
Audi Red <a>10</a><c>50-c</c>
KIA Green <a>20</a><c>51-c</c>