如何使用存储过程使用sql server 2008中的xml数据更新sql表
如何使用存储过程使用sql server 2008中的xml数据更新sql表
提问于 2015-09-28 10:55:32
我的桌子看起来像
语言ID
(Primary Key)HOSPITAL ID LANGUAGE ID下面是insert查询。
INSERT INTO [dbo].[HOSPITAL_SPOKEN_LANGUAGE]
(HOSPITAL ID, LANGUAGE ID)SELECT hospitalid,LanguageId
FROM OPENXML(@XmlHandleLANGUAGE,'/ArrayOfSpokenLanuageInfo/SpokenLanuageInfo',2)
WITH ( LanguageId INT,hospitalid INT )下面是我所拥有的XML。
<?xml version="1.0"?> <ArrayOfSpokenLanuageInfo
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <SpokenLanuageInfo>
<LanguageId>8</LanguageId>
<hospitalid>19</hospitalid> </SpokenLanuageInfo> <SpokenLanuageInfo>
<LanguageId>13</LanguageId>
<hospitalid>19</hospitalid> </SpokenLanuageInfo> <SpokenLanuageInfo>
<LanguageId>26</LanguageId>
<hospitalid>2</hospitalid> </SpokenLanuageInfo> <SpokenLanuageInfo>
<LanguageId>52</LanguageId>
<hospitalid>2</hospitalid> </SpokenLanuageInfo> </ArrayOfSpokenLanuageInfo>我希望从XML向表中插入新项，从XML插入现有项更新表。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-09-28 11:42:52
试试这个..。
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #temp
DECLARE @xml xml
Set @xml= N'
<ArrayOfSpokenLanuageInfo>
<SpokenLanuageInfo>
<LanguageId>8</LanguageId>
<hospitalid>19</hospitalid>
</SpokenLanuageInfo>
<SpokenLanuageInfo>
<LanguageId>13</LanguageId>
<hospitalid>19</hospitalid>
</SpokenLanuageInfo>
</ArrayOfSpokenLanuageInfo>'
SELECT
doc.col.value('LanguageId[1]', 'nvarchar(100)') LanguageId
,doc.col.value('hospitalid[1]', 'nvarchar(100)') hospitalid into #t
FROM @xml.nodes('/ArrayOfSpokenLanuageInfo/SpokenLanuageInfo') doc(col)
insert into [dbo].[HOSPITAL_SPOKEN_LANGUAGE] ([LANGUAGE ID],[HOSPITAL ID]) (select * from #t)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32821285
复制相关文章
相似问题
腾讯云开发者
