无法将XML值插入SQL Server [英] Cannot insert XML value into SQL server
问题描述
大家好,
我有一个xml作为输入在SQL SERVER中
i想要将xml值插入到一个sql表中
my xml是,
< main < span class =code-keyword>>
< 数据 >
< ID > CB08 < / Loca >
< 详情 >
< Itemcode > 135 < /商品代码 >
< 项目 > test < /商品 >
< / Details >
< 详细信息 >
< 项目代码 > 136 < / Itemcode >
< 项目 > test1 < / Item >
< / Details >
< / Data >
< / main >
我的问题是,
当我执行上面的xml时,它已被插入只有第一个商品代码(第一行(详情)第一行)
但第二行商品代码没有插入。
但我想插入两行进入表格
我的查询是,
CREATE TABLE #temp
(
id VARCHAR( 100 ),
ItemCode INT
)
I NSERT #temp
SELECT
Data。 value (' (Location / text())[1]',' VARCHAR(100) ')AS位置,
数据。值(' (Details / ItemCode / text())[1]',' INT ')AS ItemCode
FROM
@ XML.nodes(' / main / Data')AS AdviceData(Data)
请< span class =code-sdkkeyword>让我我知道如何将行插入一个表
感谢提前
我的尝试:
无法将xml值插入sql server
你的XML仍然不正确 - 我认为它看起来像这样:
< main> ;
< Data>
< Location> CB08< / Location>
<详情>
< ItemCode> 135< / ItemCode>
< Item> test< / Item>
< / Details>
<详情>
< ItemCode> 136< / ItemCode>
< Item> test1< / Item>
< / Details>
< / Data>
< / main>
您只获得一行的原因是因为您选择< Data>
节点,只出现一次。相反,您需要选择< Details>
节点,并使用XPath导航到< Location>
节点:
CREATE 表# temp
(
id VARCHAR ( 100 ),
ItemCode < span class =code-keyword> INT
);
INSERT #temp
SELECT
数据。 value(' (../ Location / text())[1]',' VARCHAR(100)') AS 位置,
Data.value(' (ItemCode / text())[1]',' INT') AS ItemCode
< span class =code-keyword> FROM
@ XML .nodes(' / main / Data / Details') AS AdviceData(Data)
;
然后会给你两行:
id ItemCode
--------- ----------
CB08 135
CB08 136
Hi All,
I have one xml as input IN SQL SERVER
i want to insert xml value into one sql table
my xml is ,
<main>
<Data>
<ID>CB08</Loca>
<Details>
<Itemcode>135</Itemcode>
<Item>test</Item>
</Details>
<Details>
<Itemcode>136</Itemcode>
<Item>test1</Item>
</Details>
</Data>
</main>
MY problem is,
when i execute the above xml, it has been insert only first itemcode(first row from first (details))
but second row itemcode not inserting.
but i want to insert both rows into a table
my query is ,
CREATE TABLE #temp
(
id VARCHAR(100),
ItemCode INT
)
INSERT #temp
SELECT
Data.value('(Location/text())[1]','VARCHAR(100)') AS Location,
Data.value('(Details/ItemCode/text())[1]','INT') AS ItemCode
FROM
@XML.nodes('/main/Data')AS AdviceData (Data)
So please let me know how can i insert both rows into one table
THANKS IN ADVANCE
What I have tried:
cannot insert xml value into sql server
Your XML is still not correct - I assume it was meant to look like this:
<main> <Data> <Location>CB08</Location> <Details> <ItemCode>135</ItemCode> <Item>test</Item> </Details> <Details> <ItemCode>136</ItemCode> <Item>test1</Item> </Details> </Data> </main>
The reason you're only getting one row is because you are selecting the<Data>
node, which only appears once. Instead, you need to select the<Details>
nodes, and use XPath to navigate up to the<Location>
node:
CREATE TABLE #temp ( id VARCHAR(100), ItemCode INT ); INSERT #temp SELECT Data.value('(../Location/text())[1]','VARCHAR(100)') AS Location, Data.value('(ItemCode/text())[1]','INT') AS ItemCode FROM @XML.nodes('/main/Data/Details') AS AdviceData (Data) ;
That will then give you two rows:
id ItemCode ------------------- CB08 135 CB08 136
这篇关于无法将XML值插入SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!