如何遍历 T-SQL 中 XML 字段的节点? [英] How do I iterate through the Nodes of a XML Field in T-SQL?
问题描述
我有一个 XML 字段,我知道其中至少会有一个ChildNode",但可能更多.我试图在 T-SQL 中创建一个循环,它将每个 ChildNode 的 XML 作为 VarChar(1000) 并用它做一些逻辑.当我尝试以下...
I have an XML field that I know will have at least one "ChildNode" in it, but possibly more. I am trying to make a loop in T-SQL that will get the XML of each ChildNode as a VarChar(1000) and do some logic with it. When I try the following...
...
SET @intIterator=1
SET @strValue = (SELECT XMLField.value('(/RootNode/ParentNode/ChildNode)[' + CAST(@intIterator AS VARCHAR(2)) + ']', VARCHAR(1000)) WHERE PrimaryKeyField=@intID)
WHILE LEN(@strValue) > 0
BEGIN
--LOGIC with @strValue not shown.
@intIterator = @intIterator + 1
@strValue = (SELECT XMLField.value('(/RootNode/ParentNode/ChildNode)[' + CAST(@intIterator AS VARCHAR(2)) + ']', VARCHAR(1000)) WHERE PrimaryKeyField=@intID)
END
我收到以下错误:xml 数据类型方法value"的参数 1 必须是字符串字面量.
I get the following error: The argument 1 of the xml data type method "value" must be a string literal.
我知道当我尝试在 value 方法中使用 @intIterator 时它会爆炸,因为它需要字符串文字而不是变量,但是如何在 T-SQL 中逐个遍历子节点?
I know that it's blowing up when I try to use the @intIterator in the value method as it wants a string literal instead of a variable, but then how can I iterate through the child nodes one by one in T-SQL?
推荐答案
我不知道您的 XML 是什么样子,但您可能必须使用不同的方法 - 不要尝试迭代和循环之类的东西像那样 - 而是在 XQuery 中使用 .nodes()
函数:
I don't have any idea what your XML looks like, but you probably have to use a different approach - don't try to iterate and loop and stuff like that - instead use the .nodes()
function in XQuery:
SELECT
Child.value('(SomeElement)[1]', 'int'),
Child.value('(SomeOtherElement)[1]', 'Varchar(50)')
FROM
XMLField.nodes("/RootNode/ParentNode/ChildNode") AS N(Child)
这基本上将迭代/循环留给了 XQuery,而您根本不必处理索引或类似的东西.....
That basically leaves the iterating / looping to XQuery and you don't have to mess around with indices or anything like that at all.....
这篇关于如何遍历 T-SQL 中 XML 字段的节点?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!