TSQL for XML CROSS apply to all nodes

Is it possible to beat that requirments for XML saying set of nodes is required for 'nodes()'. In MY example I'm trying to get all values in Data section but list of /Submission/Measures/* is not stable so I'd like to add dynamics to it if it's possible.
Solution below is working with hard coded list in CROSS APPLY, I'm trying to get all node info without specifying the list.

Thanks all and Mr.Khabinsky especially who helped me with first part.

My setup: Microsoft SQL Server 2017 (RTM-CU22)

Desired output:

And runnable code, refer to <== tag for needed change

DECLARE @xml XML =   N'<Submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">      <Measures>          <A100 versionID="A01">              <Metadata>                  <TY_Year>2020</TY_Year>                  <Benefit>true</Benefit>              </Metadata>              <A100_Description>                  <Stratification>                      <Pool>11-14</Pool>                  </Stratification>                  <Data>                      <RegTotal>13071</RegTotal>                      <CountA>542</CountA>                  </Data>              </A100_Description>              <A100_Description>    <Stratification>     <Pool>4-6</Pool>         </Stratification>                  <Data>   <RegTotal>8995</RegTotal>     <CountA>366</CountA>     </Data>              </A100_Description>              <A100_Description>   <Stratification>     <Pool>7-10</Pool>         </Stratification>                  <Data>   <RegTotal>11818</RegTotal>   <CountA>546</CountA>      </Data>              </A100_Description>          </A100>          <B200 versionID="B12">              <Metadata>                  <TY_Year>2020</TY_Year>                  <Benefit>true</Benefit>              </Metadata>              <B200_Description>                  <Stratification>                      <Pool>12-18</Pool>                  </Stratification>                  <Data>                      <RegTotal>349</RegTotal>                      <X5>12</X5>                      <CountA>269</CountA>                      <CountB>0</CountB>                  </Data>              </B200_Description>          </B200>          <z300 versionID="B33">              <z300_Descr>                  <Stratification>                      <Pool>19-50</Pool>                  </Stratification>                  <Data>                      <RegTotal>33333</RegTotal>                      <X5>3333</X5>                      <CountA>333</CountA>                      <CountB>33</CountB>                  </Data>              </z300_Descr>        </z300>      </Measures>  </Submission>';  -- DDL and sample data population, end    SELECT r.value('local-name(.)', 'VARCHAR(30)') AS [Msr]        , c.value('(Stratification/Pool/text())[1]', 'VARCHAR(30)') AS [Pool]      , c.value('(Data/RegTotal/text())[1]', 'INT') AS [RegTotal]      , c.value('(Data/X5/text())[1]', 'INT') AS [X5]      , c.value('(Data/CountA/text())[1]', 'INT') AS [CountA]      , c.value('(Data/CountB/text())[1]', 'INT') AS [CountB]   FROM @xml.nodes('/Submission/Measures/*') AS p(r)   CROSS APPLY p.r.nodes('A100_Description, B200_Description, z300_Descr') AS t(c);     --How CROSS Apply to all nodes without listing them (to get all 3) ????   --  CROSS APPLY p.r.nodes('/Submission/Measures/*') AS t(c);                 --<=== set of nodes is required for 'nodes()'      

https://stackoverflow.com/questions/67014299/tsql-for-xml-cross-apply-to-all-nodes April 09, 2021 at 10:32AM