本文介绍了无法将XML值插入SQL Server的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 大家好, 我有一个xml作为输入在SQL SERVER中 i想要将xml值插入到一个sql表中 my xml是, < main > < 数据 > < 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) 请让我我知道如何将行插入一个表 感谢提前 我的尝试: 无法将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 INT ); INSERT #temp SELECT 数据。 value(' (../ Location / text())[1]',' VARCHAR(100)') AS 位置, Data.value(' (ItemCode / text())[1]',' INT') AS ItemCode 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 SERVERi want to insert xml value into one sql tablemy 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 tablemy query is ,CREATE TABLE #temp(id VARCHAR(100),ItemCode INT )INSERT #tempSELECTData.value('(Location/text())[1]','VARCHAR(100)') AS Location,Data.value('(Details/ItemCode/text())[1]','INT') AS [email protected]('/main/Data')AS AdviceData (Data)So please let me know how can i insert both rows into one tableTHANKS IN ADVANCEWhat 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 #tempSELECT Data.value('(../Location/text())[1]','VARCHAR(100)') AS Location, Data.value('(ItemCode/text())[1]','INT') AS ItemCodeFROM @XML.nodes('/main/Data/Details') AS AdviceData (Data);That will then give you two rows:id ItemCode-------------------CB08 135CB08 136 这篇关于无法将XML值插入SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 09-11 18:11