将xml插入sql server [英] inserting xml to sql server

问题描述

大家好;



我在xml的插入部分有一些问题xml

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>

尝试打开此xml时我使用了以下代码

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 ^_^

推荐答案

你好，



而不是您的选择查询使用下面的查询和检查结果，

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.

试试吧。这是对AmitGajjar发布的查询的略微修改

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.

这篇关于将xml插入sql server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！