Mix XML Parents and Children in MSSQL

I think what I need is CROSS APPLY, but I can't seem to figure out the syntax. I have a set of XML in an MSSQL 2008 database that looks like this:

<Cookie>
   <id>Chocolate Chip</id>
   <ChocolateChipPeanutButter>
      ...
   </ChocolateChipPeanutButter>

   <ChocolateChipPecan>
      ...
   </ChocolateChipPecan>
</Cookie>

<Cookie>
   <id>Raisin</id>
</Cookie>

<Cookie>
   <id>Coconut</id>
</Cookie>

<Cookie>
   <id>Sugar</id>
</Cookie>

I'm trying to chop the XML up so that I have a result set that looks like this:

Cookie Name                   Cookie SubName
___________                   ______________
Chocolate Chip                <null>
Chocolate Chip                ChocolateChipPeanutButter
Chocolate Chip                ChocolateChipPecan
Raisin                        <null>
Coconut                       <null>
Sugar                         <null>

I think I need to write something like this (assume the XML data is stored in DECLARE @XMLData XML :

SELECT
     TheXML.TheCookie.query('data(./id)')     AS CookieName
   , TheXML.TheCookie.query('.')              AS CookieData
   , Sub.SubCookieName                        AS SubCookieName
FROM
   @XMLData.nodes('//Cookie') AS TheXML(TheCookie)
CROSS APPLY
   (
       SELECT
          TheCookieXML.SubCookieName.query('local-name(.)')
       FROM
          CookieData.nodes('./*') AS TheCookieXML(SubCookieName)
   )

I know, I know, this XML schema is terrible for what I'm trying to do with it, but lets assume that we're stuck with the data this way and work from there. Am I on the right track? Will CROSS APPLY ever work like this? Can even Batman save me now?

From stackoverflow

• Try something like this:

  SELECT
       TheXML.TheCookie.value('(id[1])', 'varchar(20)') AS CookieName
     , TheXML.TheCookie.query('.') AS CookieData
     , Sub.SubCookie.value('local-name(.)', 'varchar(20)') AS SubCookieName
  FROM
     @xmlcon.nodes('//Cookie') AS TheXML(TheCookie)
  CROSS APPLY
      TheXML.TheCookie.nodes('./*') as Sub(SubCookie)
  

  Trouble is - this also selects the "id" node :-(

  Marc

  Tomalak : TheXML.TheCookie.nodes('./*[local-name() != 'id']) as Sub(SubCookie)

  brian : perfect, thanks guys!

  Tomalak : Third attempt of a comment... The above contains some single quote glitches, nothing serious. Alternative, simpler XPath: TheXML.TheCookie.nodes('./*[not(self::id)]')

  marc_s : Excellent comment, Tomalak! Thanks - nice technique for excluding nodes!

  brian : Tomalak, that XPATH was very helpful, I wish you could get rep from votes on comments.

  Tomalak : @brian: Glad to help. Virtual rep is also accepted. :-D

  Tomalak : @marc_s: Oh, I nearly forgot. +1 for the answer.