Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# XML SQL Server
Hi all;
 
I have some problem in insert part of xml as xml
 
   <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
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:
 
Name        Color        Detail
----        ----         -----
Audi        Red           1050-c
KIA         Green         2051-c
 

but what I need is:
 
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 21:29pm
Edited 2-Dec-12 22:28pm
__TR__28.9K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Instead of your select query use below query and check result,
 
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.
  Permalink  
v2
Comments
__TR__ at 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 at 3-Dec-12 3:27am
   
No issue. Important thing is, OP should get his resolution.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try this. It is a slight modification to the query posted by AmitGajjar
 
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.
  Permalink  
Comments
@AmitGajjar at 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__ at 3-Dec-12 3:30am
   
Thanks.
eng.dzdz at 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__ at 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>

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

  Print Answers RSS
0 OriginalGriff 495
1 Maciej Los 340
2 Richard MacCutchan 265
3 BillWoodruff 225
4 Mathew Soji 200
0 OriginalGriff 8,804
1 Sergey Alexandrovich Kryukov 7,457
2 DamithSL 5,689
3 Maciej Los 5,279
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web04 | 2.8.1411028.1 | Last Updated 3 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100