Hi following is my sample data
Code:
DECLARE @XML XML
SELECT @XML = '<Parent1>
	<Parent1Val1>P1</Parent1Val1>
	<Parent11>
		<Parent11Val1>P11</Parent11Val1>
		<Parent12Parent1>
			<Parent122Paren1Val1>P121</Parent122Paren1Val1>
			<Parent122Parnet2>
				<Parent2Parnet2Val1>P1221</Parent2Parnet2Val1>
				<Parent2Parnet2Val2>P1222</Parent2Parnet2Val2>
			</Parent122Parnet2>
		</Parent12Parent1>
	</Parent11>	
</Parent1>'
for the above given input i want to get
Code:
Desirede Output
----------------------
Parent1Val1		Parent11Val1 Parent122Paren1Val1		Parent2Parnet2Val1
P1			P11		P121			P1221
P1			P11		P121			P1222
i tried with this but i does not giving parent node value
Code:
SELECT	t.Ord.value('local-name(..)[1]', 'VARCHAR(100)') AS ParentNodeName,
	t.Ord.value('local-name(.)[1]', 'VARCHAR(100)') AS NodeName,
	t.Ord.value('text()[1]', 'VARCHAR(100)') AS NodeText
FROM	@XML.nodes('//*') T(Ord)
Thanks in advance