sql - 需要帮助解析 XML 字符串并在存储过程中插入值

Question

我正在尝试编写一个存储过程，在其中从 XML 字符串插入一些内容。
这是为了避免从循环内部进行大量存储过程调用。

这是我到目前为止得到的，但它给了我错误:

Msg 207, Level 16, State 1, Procedure newsMapper_prc, Line 22
Invalid column name 'headline'.
Msg 207, Level 16, State 1, Procedure newsMapper_prc, Line 22
Invalid column name 'story'.
Msg 207, Level 16, State 1, Procedure newsMapper_prc, Line 25
Invalid column name 'entity'.
Msg 207, Level 16, State 1, Procedure newsMapper_prc, Line 27
Invalid column name 'entity'.

无法真正弄清楚这一点，将不胜感激

<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

Answer 1

鉴于您的 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“拆分”为行和列:

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()

然后你想在 NewsEntities 中添加一个条目 <entity> 的每个值的表格在您的 XML 中 - 正确吗？

INSERT INTO dbo.NewsEntities(NewsID, EntityID)
    SELECT
        @NewsID,
        NF.Ent.value('(.)[1]', 'int')
FROM
    @input.nodes('/newsfile/entity') AS NF(Ent)

如果你把这一切放在一起 - 这能解决你的问题吗？=