Parsing XML from a SQL Server table column into multiple rows

Question

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

Answer 1

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.