Help finding nodes based on atributes in xml

Posted on 2012-08-15
Last Modified: 2012-08-30
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"
Question by:pucko73
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Please post a sample Xml document and the expected result.

    Author Comment

      <node1  name="test">
          <subnode1 name "subtest1">
      <node2  name="test2">
          <subnode2 name "subtest2">
          </ubsub1 name='subsubtest"> </subsub1>



    a good result for this would be a table walue

    Col Node:                                     name
    /root                                            Null
    /root/node1                                 test
    /root/node1/subnode1                subtest1
    /root/node2                                 test2
    /root/node2/subnode2                subtest2
    /root/node2/subnode2/subsub1  subsubtest
    LVL 9

    Accepted Solution

    you can use "openxml" command for this

    Refer below link for more details....
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    Unfortunately the Xml you posted is not well-formed.  I corrected it as best I could and here is the result:
    DECLARE @Xml xml =
    	<node1 name="test"> 
    		<subnode1 name="subtest1">
    	<node2 name="test2">
    		<subnode2 name="subtest2">
    			<subsub1 name="subsubtest"></subsub1>
    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)
    -------------------- -------------------- -------------------- -------------------- --------------------
    test                 subtest1             test2                subtest2             subsubtest

    Open in new window


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how the fundamental information of how to create a table.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now