Querying XML data from a database and returning multiple rows for an element

[dgreenberg]

Running a query against an XML table in a database can present certain challenges.

One of the more difficult ones was figuring out how to get multiple values out of a record in an XML table.

XMLdata:
<root1>
     <R>
           <period>2019-Q1</period>
           <period>2019-Q2</period>
           <period>2018-Q1</period>
           <period>2018-Q2</period>
           <Tgroup>117</Tgroup>
           <Tgroup>231</Tgroup>
           <ToData>12</ToData>
           <ToData>241</ToData>
           <ToData>216</ToData>
     </R>
</root1>

As you can see the 3 fields (period,Tgroup,ToData) all have multiple values for this record.

Normally we would write a SQL query like this one:

select
INumber,
N.C.value('(R/period)[1]','nvarchar(max)') as period, N.C.value('(R/TGroup)[1]','nvarchar(max)') as Tgroup,
N.C.value('(R/ToData)[1]','nvarchar(max)') as ToData,
from DataTable cross apply [XMLdata].nodes('/root') as N(C);

The problem with the above query is only 1 value will be returned for those 3 fields no matter how many exist in the XML for that record.

To solve this problem use a query like the one below.

select
EF.INumber,
XMLdata,
period,
TGroup,
ToData
FROM DataTable EF
Cross apply (
           SELECT distinct
                  INumber,
                  c.value('.[1]', 'varchar(200)') as period,
                  d.value('.[1]', 'varchar(200)') as TGroup,
                  e.value('.[1]', 'varchar(200)') as ToData
           FROM DataTable A
      outer apply XMLdata.nodes('/root1/R/period') as T(C)
      outer apply XMLdata.nodes('/root1/R/TGroup') as U(D)
     outer apply XMLdata.nodes('/root1/R/ToData') as V(e)
     where EF.INumber=A.INumber
)A