节点在SQL 2005 + XML中的位置 [英] Position of node in SQL 2005 + XML

问题描述

DECLARE @GroupXML  AS XML
 
SET @GroupXML=
'<GroupA>
<ParantNode Id="001">
   <ChildNode Name ="AA01"> </ChildNode>
   <ChildNode Name ="AA02"> </ChildNode>
</ParantNode>
<ParantNode Id="002">
   <ChildNode Name ="BB"> </ChildNode>
</ParantNode>
</GroupA>'
 
INSERT INTO @GroupTable (ParentIndex,ChildeName)
SELECT       
 NodeIndex,--Id of ParantNode of GroupA   
 GroupXML.GX.value('@Name', 'NVARCHAR(50)') ChildName          
FROM       
  @GroupXML.nodes('/GroupA/ParantNode/ChildNode') AS GroupXML(GX)  (ParentIndex,ChildeName)


SELECT * FROM @GroupTable

是否可以检索节点的索引?请让我知道如何在"NodeIndex，-GroupA的ParantNode的ID"中编写正确的查询
这样才能得出以下结果?

ParentIndex	ChildeName
1	AA01
1	AA02
2	BB

Is it possible to retrieve index of node ? Please let me know how we can write proper query in " NodeIndex,--Id of ParantNode of GroupA"
So that it will give below result ?

ParentIndex	ChildeName
1	AA01
1	AA02
2	BB

-----------------------
ParentIndex | ChildeName
------------------------
     1      |    AA01   
     1      |    AA02  
     2      |    BB

编辑DMA:使用html进行表标记，并将原始标记切换到pre块，以向OP演示如何整齐且对齐地显示表.

Edit DMA: Table markup using html, and original switched to pre block to demonstrate to OP ways of displaying a table neatly and aligned.

推荐答案

在这里是:

Here it is :

DECLARE @GroupXML  AS XML
 
SET @GroupXML=
'<GroupA>
<ParantNode Id="001">
   <ChildNode Name ="AA01"> </ChildNode>
   <ChildNode Name ="AA02"> </ChildNode>
</ParantNode>
<ParantNode Id="002">
   <ChildNode Name ="BB"> </ChildNode>
</ParantNode>
</GroupA>'

SELECT cast( GroupXML.GX.value('../@Id', 'NVARCHAR(50)') as int) ParentIndex, GroupXML.GX.value('@Name', 'NVARCHAR(50)') ChildName
	FROM  @GroupXML.nodes('/GroupA/ParantNode/ChildNode') AS GroupXML(GX)

看看这些页面:
http://msdn.microsoft.com/en-us/library/ms178030％28v = sql.100％29.aspx [ ^ ]

http://www.w3schools.com/xpath/xpath_syntax.asp [

Have look at these pages :
http://msdn.microsoft.com/en-us/library/ms178030%28v=sql.100%29.aspx[^]

http://www.w3schools.com/xpath/xpath_syntax.asp[^]

BTW, I''m not in western World ;)

Hope it helps.

这篇关于节点在SQL 2005 + XML中的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！