需要帮助解析XML字符串并在存储过程中插入值
问题描述:
我想写一个存储过程,其中我从XML字符串中插入了一些东西。 这是为了避免在循环中进行大量的存储过程调用。需要帮助解析XML字符串并在存储过程中插入值
这是我走到这一步,但它给我的错误:
消息207,级别16,状态1,过程newsMapper_prc,第22行
无效的列名 '标题'。
消息207,级别16,状态1,过程newsMapper_prc,行22
无效的列名'故事'。
消息207,级别16,状态1,过程newsMapper_prc,行25
无效的列名'实体'。
消息207,级别16,状态1,过程newsMapper_prc,行27
无效的列名称'实体'。
真的不能想出解决办法,一些帮助,将不胜感激
<newsfile>
<headline>THIS IS A NEWS HEADLINE</headline>
<story>THIS IS A NEWS STORY</story>
<entity> 1234</entity>
<entity>1111</entity>
<entity>2222</entity>
</newsfile>
存储过程的代码:
CREATE PROCEDURE newsMapper_prc
-- Add the parameters for the stored procedure here
(@xmlString xml)
AS
declare @criteriaTable table (criterianame varchar(100), parm varchar(MAX))
insert into @criteriaTable
Select
criteriaValues.parm.value('../@type','varchar(MAX)'),
criteriaValues.parm.value('.','varchar(MAX)')
from @xmlString.nodes('/newsfile/headline') as headline(parm),
@xmlString.nodes('/newsfile/headline/story') as story(parm),
@xmlString.nodes('/newsfile/headline/story/entity') as entity(parm)
insert into News (newsHeadline, newsStory, newsDate) values ((select headline from @criteriaTable),(select story from @criteriaTable), GETDATE())
declare @newsID int
SET @newsID = scope_identity()
while exists (select entity from @criteriaTable)
BEGIN
insert into NewsEntities(newsID,entityID) values (@newsID,(Select entity from @criteriaTable))
END
答
考虑使用这个你的XML
DECLARE @input XML
SET @input = '<newsfile>
<headline>THIS IS A NEWS HEADLINE</headline>
<story>THIS IS A NEWS STORY</story>
<entity> 1234</entity>
<entity>1111</entity>
<entity>2222</entity>
</newsfile>'
T-SQL语句,你可以“撕开”你的XML int Ø行和列:
SELECT
NF.Ent.value('(.)[1]', 'int') AS 'Entity',
@input.value('(/newsfile/headline)[1]', 'varchar(100)') AS 'Headline',
@input.value('(/newsfile/story)[1]', 'varchar(100)') AS 'Story'
FROM
@input.nodes('/newsfile/entity') AS NF(Ent)
这给你:
Entity Headline Story
1234 THIS IS A NEWS HEADLINE THIS IS A NEWS STORY
1111 THIS IS A NEWS HEADLINE THIS IS A NEWS STORY
2222 THIS IS A NEWS HEADLINE THIS IS A NEWS STORY
现在你怎么想这个信息办??我不是很了解你的存储过程是试图做.....
如果我理解正确的 - 你要插入的标题和故事到News
表,并获得IDENTITY ID后面:
INSERT INTO dbo.News(newsHeadline, newsStory, newsDate)
SELECT
@input.value('(/newsfile/headline)[1]', 'varchar(100)') AS 'Headline',
@input.value('(/newsfile/story)[1]', 'varchar(100)') AS 'Story',
GETDATE()
DECLARE @NewsID INT
SET @NewsID = SCOPE_IDENTITY()
然后你想在你的XML中为每个<entity>
的值在NewsEntities
表中添加一个条目 - 正确吗?
INSERT INTO dbo.NewsEntities(NewsID, EntityID)
SELECT
@NewsID,
NF.Ent.value('(.)[1]', 'int')
FROM
@input.nodes('/newsfile/entity') AS NF(Ent)
如果你把所有这些组合起来 - 这是否解决您的问题=
我想这意味着是SQL Server的?哪个版本? –