Parsing XML from a SQL Server table column into multiple rows

SQL Server Standard 2017 on Windows Server 2019

I have a SQL Server table that contains an XML column to capture custom data

create table CustomerCrossReferences 
(
    customer BigInt, 
    product BigInt, 
    customColumns XML
)

The customColumns column contains XML data in the following format

insert into CustomerCrossReferences 
values (69731, 157,  
'<CustomColumnsCollection>
  <CustomColumn>
    <Name>MOI</Name>
    <DataType>0</DataType>
    <Value>10% max</Value>
  </CustomColumn>
  <CustomColumn>
    <Name>PRO</Name>
    <DataType>0</DataType>
    <Value>26% min</Value>
  </CustomColumn>
  <CustomColumn>
    <Name>FAT</Name>
    <DataType>0</DataType>
    <Value />
  </CustomColumn>
  <CustomColumn>
    <Name>WAT</Name>
    <DataType>0</DataType>
    <Value />
  </CustomColumn>
  <CustomColumn>
    <Name>FMT</Name>
    <DataType>0</DataType>
    <Value>None</Value>
  </CustomColumn>
</CustomColumnsCollection>')

I need to use this data to build a dataset in a report built in SQL Server Report Writer, selecting Value corresponding to the Name for the Customer/Product pairing. I won't be using DataType in the report.

At the moment I am thinking I should create a dataset in the following format to use in within my report structure

Customer    Product    Name   DataType   Value
-------------------------------------------------
69731       157        MOI    0          10% max
69731       157        PRO    0          26% min
69731       157        FAT    0          
69731       157        WAT    0          
69731       157        FMT    0          

I can create this dataset with the following:

select
  customer
  ,product 
  ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Name)[1]', 'varchar(max)')
  ,customColumns.value('(/CustomColumnsCollection/CustomColumn/DataType)[1]', 'varchar(max)')
  ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Value)[1]', 'varchar(max)')
from
  CustomerCrossReferences
UNION ALL
select
  customer
  ,product 
  ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Name)[2]', 'varchar(max)')
  ,customColumns.value('(/CustomColumnsCollection/CustomColumn/DataType)[2]', 'varchar(max)')
  ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Value)[2]', 'varchar(max)')
from
  CustomerCrossReferences
UNION ALL
select
  customer
  ,product 
  ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Name)[3]', 'varchar(max)')
  ,customColumns.value('(/CustomColumnsCollection/CustomColumn/DataType)[3]', 'varchar(max)')
  ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Value)[3]', 'varchar(max)')
from
  CustomerCrossReferences

While this works it will be cumbersome as I have approx 20 CustomColumn data elements to consider.

My question is whether there is a better way of handling this, either as a more concise/efficient query statement, or by being able to directly address the XML data within Report Builder. Thanks

You can use XQuery nodes() method to get your xml data in row format. something like:

SELECT	cc.customer, cc.product
,	n.value('(Name/text())[1]', 'nvarchar(100)') AS Name
,	n.value('(DataType/text())[1]', 'int') AS DataType
,	n.value('(Value/text())[1]', 'nvarchar(100)') AS Value
FROM	CustomerCrossReferences cc
CROSS APPLY customColumns.nodes('/CustomColumnsCollection/CustomColumn') n(n)

Combined with value() method, you can retrieve individual elements of CustomColumn.