SQL openXML 如何使用for循环

Question

这是我的代码，我在逐一插入 xml 元素值时遇到问题。 如何为此设置 for 循环

DECLARE @x VARCHAR(8000)
SET @x = 
  '<authors>
    <author>
        <firstname>Michael</firstname>
        <lastname>Howard</lastname>
    </author>
    <author>
        <firstname>David</firstname>
        <lastname>LeBlanc</lastname>
    </author>
    <author>
        <firstname>adad</firstname>
        <lastname>asdad</lastname>
    </author>
     <author>
        <firstname>adad</firstname>
        <lastname>asdad</lastname>
    </author>
   </authors>'

DECLARE @h INT
DECLARE @h1 VARCHAR(MAX)
EXECUTE sp_xml_preparedocument @h OUTPUT, @x 

SELECT @h1= firstname 
FROM OPENXML(@h, '/authors/author',2)
WITH(
    firstname VARCHAR(20)

)
select NAME from temps WHERE NAME=@h1 
if @@ROWCOUNT=0
Begin
INSERT INTO temps(NAME)  VALUES (@h1)
END
EXECUTE sp_xml_removedocument @h 

现在我的代码只插入最后一个 xml 元素，我想一个接一个地执行此操作..告诉我解决方案或想法

Answer 1

 DECLARE @x XML  = '<authors>
  <author>
    <firstname>Michael</firstname>
    <lastname>Howard</lastname>
  </author>
  <author>
    <firstname>David</firstname>
    <lastname>LeBlanc</lastname>
  </author>
  <author>
    <firstname>adad</firstname>
    <lastname>asdad</lastname>
  </author>
  <author>
    <firstname>adad</firstname>
    <lastname>asdad</lastname>
  </author>
</authors>'

 SELECT T.c.value('./firstname[1]', 'varchar(200)')
 FROM @x.nodes('/authors/author') AS T(c)

Answer 2

如果你这样做：

 DECLARE @x xml
SET @x = 
  '<authors>
    <author>
        <firstname>Michael</firstname>
        <lastname>Howard</lastname>
    </author>
    <author>
        <firstname>David</firstname>
        <lastname>LeBlanc</lastname>
    </author>
    <author>
        <firstname>adad</firstname>
        <lastname>asdad</lastname>
    </author>
     <author>
        <firstname>adad</firstname>
        <lastname>asdad</lastname>
    </author>
   </authors>'

SELECT 
  ref.value('firstname[1]', 'NVARCHAR(20)') AS firstname ,
  ref.value('lastname[1]', 'NVARCHAR (10)') AS lastname ,
  ROW_NUMBER() over (order by ref.value('firstname[1]', 'NVARCHAR(20)')) AS rownum 

FROM @x.nodes('/authors/author') 
xmlData( ref )

Which prints: 
firstname            lastname   rownum
-------------------- ---------- --------------------
adad                 asdad      1
adad                 asdad      2
David                LeBlanc    3
Michael              Howard     4

你可以轻松地执行insert into ...而不是执行循环

NOTE我添加了row_num，因为它看起来你只想插入我从您的查询中了解到的第二行的值

Answer 3

得到答案......

DECLARE @x VARCHAR(8000),
        @nItemCnt int
DECLARE @ProductID VARCHAR(MAX),
        @lcnt INT
DECLARE @@getCurrentName CURSOR,
@h INT,
@h1 VARCHAR(MAX)
SET @x = 
  '<authors>
    <author>
        <firstname>Michael</firstname>
        <lastname>Howard</lastname>
    </author>
    <author>
        <firstname>David</firstname>
        <lastname>LeBlanc</lastname>
    </author>
    <author>
        <firstname>adad</firstname>
        <lastname>asdad</lastname>
    </author>
     <author>
        <firstname>adad</firstname>
        <lastname>asdad</lastname>
    </author>
   </authors>'


EXECUTE sp_xml_preparedocument @h OUTPUT, @x 
SET @@getCurrentName = CURSOR FOR SELECT firstname FROM OPENXML(@h, '/authors/author',2)WITH(firstname VARCHAR(20))
OPEN @@getCurrentName
FETCH NEXT FROM @@getCurrentName INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
select @lcnt=COUNT(name) from temps WHERE name=@ProductID
if @lcnt=0 
insert into temps values(@ProductID)
PRINT @ProductID
FETCH NEXT
FROM @@getCurrentName INTO @ProductID
END
CLOSE @@getCurrentName
DEALLOCATE @@getCurrentName

Answer 4

您没有描述为什么要处理 XML，所以我会猜测。看来您正在 temps 表中查找特定的 firstname 值，如果未找到，则添加它。我将使用表变量 @names 进行演示。

-- Assume you have a table [temps] with
-- a column [name], and you want to find
-- the new/missing firstnames from an XML
-- fragment and insert the new firstnames into
-- the [temps] table
-- Example:
--DECLARE [temps] TABLE (name VARCHAR(20))
--INSERT [temps] (name) VALUES ('Michael')

-- Create a temporary table to hold the XML firstname values
DECLARE @names TABLE (firstname VARCHAR(20))
DECLARE @x xml
SET @x =  
  '<authors> 
    <author> 
        <firstname>Michael</firstname> 
        <lastname>Howard</lastname> 
    </author> 
    <author> 
        <firstname>David</firstname> 
        <lastname>LeBlanc</lastname> 
    </author> 
    <author> 
        <firstname>adad</firstname> 
        <lastname>asdad</lastname> 
    </author> 
     <author> 
        <firstname>adad</firstname> 
        <lastname>asdad</lastname> 
    </author> 
   </authors>' 

-- Extract the firstnames from the XML and insert the names
-- into the temp table variable
INSERT INTO @names(firstname)
SELECT  x.author.value('firstname[1]', 'varchar(20)') AS firstname
FROM    @x.nodes('/authors/author') AS x(author)

-- Use a left outer join query to identify the new/missing names
-- in the temp table and then insert the names into [temps].
-- The WHERE condition specifies rows that exist in @firstnames
-- but do not exist (are NULL) in [temps].
INSERT  temps (name)
SELECT  nam.firstname
FROM    @names      nam  LEFT OUTER JOIN
        temps       tmp  ON (nam.firstname = tmp.name)
WHERE   tmp.name IS NULL

LEFT OUTER JOIN 与 WHERE 条件一起使用来标识 @firstnames 表变量中而不是 [firstnames] 表中的名字。只要有可能，就尽量避免在 SQL 中迭代数据，并尝试使用基于集合的操作。