Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

Help finding nodes based on atributes in xml

Hello, can someone help me
with a query that goes through a xml
and find all nodes with the atribute name
and gives the value of name atribute and value of the "NodeName"
0
pucko73
Asked:
pucko73
  • 2
2 Solutions
 
Anthony PerkinsCommented:
Please post a sample Xml document and the expected result.
0
 
pucko73Author Commented:
<root>
  <node1  name="test">
      <subnode1 name "subtest1">
      </subnode1>
  </node1>
   
  <node2  name="test2">
      <subnode2 name "subtest2">
      </ubsub1 name='subsubtest"> </subsub1>
      </subnode2>


  </node2>

</root>

a good result for this would be a table walue
with

Col Node:                                     name
/root                                            Null
/root/node1                                 test
/root/node1/subnode1                subtest1
/root/node2                                 test2
/root/node2/subnode2                subtest2
/root/node2/subnode2/subsub1  subsubtest
0
 
keyuCommented:
you can use "openxml" command for this

Refer below link for more details....

http://msdn.microsoft.com/en-us/library/ms186918.aspx
0
 
Anthony PerkinsCommented:
Unfortunately the Xml you posted is not well-formed.  I corrected it as best I could and here is the result:
DECLARE @Xml xml =
'<root>
	<node1 name="test"> 
		<subnode1 name="subtest1">
		</subnode1>
	</node1>
	<node2 name="test2">
		<subnode2 name="subtest2">
			<subsub1 name="subsubtest"></subsub1>
		</subnode2>
	</node2>
</root>'

SELECT	T.C.value('node1[1]/@name', 'varchar(20)'),
	T.C.value('node1[1]/subnode1[1]/@name', 'varchar(20)'),
	T.C.value('node2[1]/@name', 'varchar(20)'),
	T.C.value('node2[1]/subnode2[1]/@name', 'varchar(20)'),
	T.C.value('node2[1]/subnode2[1]/subsub1[1]/@name', 'varchar(20)')
FROM	@Xml.nodes('root') T(C)

Output:
                                                                                  
-------------------- -------------------- -------------------- -------------------- --------------------
test                 subtest1             test2                subtest2             subsubtest

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now