Parsing hierarchical/nested XML

Question

Hi, I have been fighting with this hierarchical XML since 3 hours and I really cannot anymore. Can anyone be so kind and help mi with that?

--code starts here 

declare @x xml;
set @x = '<root>
<row> 
<ID>1</ID> 
<OFFICE_HOURS> 
<day name="Monday"> 
<from_to> 
<from>08:00</from> 
<to>11:00</to> 
</from_to> 

<from_to> 
<from>13:00</from> 
<to>17:00</to> 
</from_to> 
</day> 

<day name="Tuesday"> 
<from_to> 
<from>08:00</from> 
<to>11:00</to> 
</from_to> 

<from_to> 
<from>13:00</from> 
<to>17:00</to> 
</from_to> 
</day> 
</OFFICE_HOURS> 
</row> 

<row> 
<ID>2</ID>
<OFFICE_HOURS> 
<day name="Monday"> 
<from_to> 
<from>08:00</from> 
<to>10:00</to> 
</from_to> 

<from_to> 
<from>15:00</from> 
<to>17:00</to> 
</from_to> 
</day> 

<dayname="Tuesday"> 
<from_to> 
<from>08:00</from> 
<to>12:00</to> 
</from_to> 
</day>
</OFFICE_HOURS> 
</row>
</root>';

--code finishes here 

The output has to be:

ID Day From To

-------------------------------------------

1 Monday 08:00 11:00

1 Monday 13:00 17:00

1 Tuesday 08:00 11:00

1 Tuesday 13:00 17:00

2 Monday 08:00 10:00

2 Monday 15:00 17:00

2 Tuesday 08:00 12:00

It would be also great if someone explains the solution a bit, it will help to others as well.

Thank you very much,

Petr

Answer 1

Try this:

DECLARE @xml XML;

SET @xml = '<root>
  <row>
    <ID>1</ID>
    <OFFICE_HOURS>
      <day name="Monday">
        <from_to>
          <from>08:00</from>
          <to>11:00</to>
        </from_to>
        <from_to>
          <from>13:00</from>
          <to>17:00</to>
        </from_to>
      </day>
      <day name="Tuesday">
        <from_to>
          <from>08:00</from>
          <to>11:00</to>
        </from_to>
        <from_to>
          <from>13:00</from>
          <to>17:00</to>
        </from_to>
      </day>
    </OFFICE_HOURS>
  </row>
  <row>
    <ID>2</ID>
    <OFFICE_HOURS>
      <day name="Monday">
        <from_to>
          <from>08:00</from>
          <to>10:00</to>
        </from_to>
        <from_to>
          <from>15:00</from>
          <to>17:00</to>
        </from_to>
      </day>
      <day name="Tuesday">
        <from_to>
          <from>08:00</from>
          <to>12:00</to>
        </from_to>
      </day>
    </OFFICE_HOURS>
  </row>
</root>';


SELECT
	r.c.value('(ID/text())[1]', 'INT') AS Id,
	oh.c.value('@name', 'VARCHAR(20)') AS [Day],
	ft.c.value('(from/text())[1]', 'VARCHAR(20)') AS [From],
	ft.c.value('(to/text())[1]', 'VARCHAR(20)') AS [To]
FROM @xml.nodes('root/row') r(c)
	CROSS APPLY r.c.nodes('OFFICE_HOURS/day') oh(c)
		CROSS APPLY oh.c.nodes('from_to') ft(c)

For explanations, try these great articles:

xml Data Type Methods
http://msdn.microsoft.com/en-us/library/ms190798(v=SQL.105).aspx

Introduction to XQuery in SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx

XML Support in Microsoft SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

Answer 2

wBob, thanks a lot, it works.