Click here to Skip to main content
14,426,260 members
Rate this:
Please Sign up or sign in to vote.
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


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 ?


ParentIndexChildeName
1AA01
1AA02
2BB


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


Edit DMA: Table markup using html, and original switched to pre block to demonstrate to OP ways of displaying a table neatly and aligned.
Posted
Updated 2-Jan-12 23:17pm
v5
Comments
OriginalGriff 3-Jan-12 3:19am
   
Don't post the same question twice - It is early in teh morning in teh Western world, and most people are just starting to arive for work.
Give it a little time for people to answer!
And then, use the "Improve question" widget to edit your question and provide better information rather than posting a new question.
I have deleted the older one and this one has fewer spelling mistakes!

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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)



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.
   
Comments
RDBurmon 3-Jan-12 5:04am
   
he he :) I will try and let you know the result
RDBurmon 3-Jan-12 6:13am
   
It worked .Thank you very much Amir , You are always a Hero for me .
Amir Mahfoozi 3-Jan-12 6:18am
   
You're welcome and thank you for the overwhelming praise 8-}

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 5,043
Richard MacCutchan 1,721
phil.o 1,260
Patrice T 1,187
MadMyche 1,015



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100