-- Setup dummy or sample data
DECLARE	@r XML

SET	@r = '
<Reference>
  <Basic>
    <Book>A1.Hj1.JU9</Book>
  </Basic>
  <App>
    <A>AK9.HL9.J0</A>
    <A>A18.H.PJ69</A>
  </App>
  <Sub>
    <B>B13.H98.P9</B>
    <B>B18.HO9.JIU8</B>
  </Sub>
  <DI>
    <D>D23.HYT.P6R</D>
  </DI>
</Reference>
'

-- get node structure
;WITH Yak (Localname, val, parent)
AS (
SELECT  T.n.value('localname[1]', 'varchar(100)') AS Localname,
	T.n.value('value[1]', 'VARCHAR(100)') AS val,
        T.n.value('parent[1]', 'VARCHAR(100)') AS parent
FROM    ( SELECT    
                    x.query('
                       for $node in /descendant::node()[local-name() != ""] 
                       return <node>
                              <localname>{ local-name($node) }</localname>
                              <value>{ $node }</value>
                              <parent>{ local-name($node/..) }</parent>
                              </node>') AS nodes
          FROM      @r.nodes('/') x(x)
        ) q1
        CROSS APPLY q1.nodes.nodes('/node') AS T ( n )
)

SELECT		y2.localname AS nodename,
		y3.val AS value
FROM		Yak AS y1
INNER JOIN	Yak AS y2 ON y2.parent = y1.localname
INNER JOIN	Yak AS y3 ON y3.parent = y2.localname

quote:

---

Originally posted by Peso

Here is a technique I've learned from Ken Simmons.

---

SELECT T.n.value('local-name(..)[1]', 'varchar(100)') AS nodename,
  T.n.value('text()[1]', 'varchar(100)') AS value
FROM ( SELECT @r AS ref ) AS Q1
CROSS APPLY Q1.ref.nodes('Reference/*/*') AS T ( n )

quote:

---

Originally posted by Arnold Fribble
I like the CROSS APPLY over the nodes, that's a neat idea

---

SELECT T.n.value('local-name(..)[1]', 'varchar(100)') AS nodename,
  T.n.value('text()[1]', 'varchar(100)') AS value
FROM @r.nodes('Reference/*/*') AS T ( n )