解析具有相同名称的多个元素的OpenXML [英] Parsing OpenXML with multiple elements of the same name

问题描述

我有这样的数据结构:

<rootnode>
  <group>
    <id>1</id>
     <anothernode>first string</anothernode>
     <anothernode>second string</anothernode>
  </group>
 <group>
   <id>2</id>
     <anothernode>third string</anothernode>
     <anothernode>fourth string</anothernode>
  </group>
</rootnode>

以及以下代码:

EXEC sp_xml_preparedocument @index OUTPUT, @XMLdoc

SELECT *
FROM OPENXML (@index, 'rootnode/group')
WITH 
(
  id int 'id',
  anothernode varchar(30) 'anothernode'
)

这给了我结果

id | anothernode
————————————————
1  | first string
2  | third string

如何获取显示结果而不是显示所有四个字符串的地方?

How can I get it to show this result instead where all four strings are showing instead?

id | anothernode
————————————————
1  | first string
1  | second string
2  | third string
2  | fourth string

推荐答案

SELECT *
FROM OPENXML (@index, 'rootnode/group/anothernode')
WITH 
(
  id int '../id',
  anothernode varchar(30) '.'
)

或者您也可以像这样使用XML数据类型:

Or you can use the XML datatype instead like this:

SELECT G.N.value('(id/text())[1]', 'int') AS id,
       A.N.value('text()[1]', 'varchar(30)') AS anothernode
FROM @XMLDoc.nodes('rootnode/group') AS G(N)
  CROSS APPLY G.N.nodes('anothernode') AS A(N)

这篇关于解析具有相同名称的多个元素的OpenXML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！