在MSSQL中混合XML父级和子级

我认为我需要的是交叉应用,但我似乎无法理解语法。我在MSSQL2008数据库中有一组XML,如下所示:

<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>

我正在尝试分割XML,以便得到如下结果集:

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

我想我需要写这样的东西(假设XML数据存储在 声明@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)
   )

我知道,我知道,这个XML模式对于我想用它做的事情来说是很糟糕的,但是让我们假设我们一直这样使用数据,并从那里开始工作。我走对了吗?交叉应用程序会像这样工作吗?现在连蝙蝠侠都能救我吗?