Question

我有这样的数据结构:

<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

Answer 1

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

或者你可以使用XML数据类型,而不是这样:

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)

友情链接