如何使用存储过程在sql server 2008中使用xml数据更新sql tabel

My table looks like

我的桌子看起来像

LANGUAGE ID

(Primary Key)HOSPITAL ID LANGUAGE ID

And following is the insert query.

以下是插入查询。

INSERT INTO [dbo].[HOSPITAL_SPOKEN_LANGUAGE] 
(HOSPITAL ID, LANGUAGE ID)SELECT hospitalid,LanguageId 
FROM OPENXML(@XmlHandleLANGUAGE,'/ArrayOfSpokenLanuageInfo/SpokenLanuageInfo',2)
WITH ( LanguageId INT,hospitalid INT )

---

Below is the XML that I have.

下面是我的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>

i want insert new items into table from XML ,existing items update table From XML .

我想从XML中插入新项目,现有项目更新表格从XML。

1 个解决方案

#1

Try this..

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)