解析来自SQL Server表列的XML并拆分为多行。

huangapple
go评论93阅读模式
英文:

Parsing XML from a SQL Server table column into multiple rows

问题

SQL Server Standard 2017 on Windows Server 2019

我有一个 SQL Server 表，其中包含一个 XML 列，用于存储自定义数据

  1. create table CustomerCrossReferences 
  2. (
  3.     customer BigInt, 
  4.     product BigInt, 
  5.     customColumns XML
  6. )

customColumns 列包含以下格式的 XML 数据

  1. insert into CustomerCrossReferences 
  2. values (69731, 157,  
  3. '<CustomColumnsCollection>
  4.   <CustomColumn>
  5.     <Name>MOI</Name>
  6.     <DataType>0</DataType>
  7.     <Value>10% max</Value>
  8.   </CustomColumn>
  9.   <CustomColumn>
  10.     <Name>PRO</Name>
  11.     <DataType>0</DataType>
  12.     <Value>26% min</Value>
  13.   </CustomColumn>
  14.   <CustomColumn>
  15.     <Name>FAT</Name>
  16.     <DataType>0</DataType>
  17.     <Value />
  18.   </CustomColumn>
  19.   <CustomColumn>
  20.     <Name>WAT</Name>
  21.     <DataType>0</DataType>
  22.     <Value />
  23.   </CustomColumn>
  24.   <CustomColumn>
  25.     <Name>FMT</Name>
  26.     <DataType>0</DataType>
  27.     <Value>None</Value>
  28.   </CustomColumn>
  29. </CustomColumnsCollection>')

我需要使用这些数据在 SQL Server Report Writer 中构建报表数据集，选择与 Customer/Product 配对的 Name 对应的 Value。我不会在报表中使用 DataType。

目前，我考虑在报表结构中使用以下格式创建数据集

  1. Customer    Product    Name   DataType   Value
  2. -------------------------------------------------
  3. 69731       157        MOI    0          10% max
  4. 69731       157        PRO    0          26% min
  5. 69731       157        FAT    0          
  6. 69731       157        WAT    0          
  7. 69731       157        FMT    0

我可以使用以下方式创建这个数据集：

  1. select
  2.   customer
  3.   ,product 
  4.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Name)[1]', 'varchar(max)')
  5.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/DataType)[1]', 'varchar(max)')
  6.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Value)[1]', 'varchar(max)')
  7. from
  8.   CustomerCrossReferences
  9. UNION ALL
  10. select
  11.   customer
  12.   ,product 
  13.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Name)[2]', 'varchar(max)')
  14.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/DataType)[2]', 'varchar(max)')
  15.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Value)[2]', 'varchar(max)')
  16. from
  17.   CustomerCrossReferences
  18. UNION ALL
  19. select
  20.   customer
  21.   ,product 
  22.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Name)[3]', 'varchar(max)')
  23.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/DataType)[3]', 'varchar(max)')
  24.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Value)[3]', 'varchar(max)')
  25. from
  26.   CustomerCrossReferences

虽然这个方法有效，但对于我需要考虑的约20个 CustomColumn 数据元素来说可能会很繁琐。

我的问题是是否有更好的处理方法，无论是更简洁/高效的查询语句，还是能够直接在 Report Builder 中访问 XML 数据。谢谢。

英文:

SQL Server Standard 2017 on Windows Server 2019

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

  1. create table CustomerCrossReferences 
  2. (
  3.     customer BigInt, 
  4.     product BigInt, 
  5.     customColumns XML
  6. )

The customColumns column contains XML data in the following format

  1. insert into CustomerCrossReferences 
  2. values (69731, 157,  
  3. '<CustomColumnsCollection>
  4.   <CustomColumn>
  5.     <Name>MOI</Name>
  6.     <DataType>0</DataType>
  7.     <Value>10% max</Value>
  8.   </CustomColumn>
  9.   <CustomColumn>
  10.     <Name>PRO</Name>
  11.     <DataType>0</DataType>
  12.     <Value>26% min</Value>
  13.   </CustomColumn>
  14.   <CustomColumn>
  15.     <Name>FAT</Name>
  16.     <DataType>0</DataType>
  17.     <Value />
  18.   </CustomColumn>
  19.   <CustomColumn>
  20.     <Name>WAT</Name>
  21.     <DataType>0</DataType>
  22.     <Value />
  23.   </CustomColumn>
  24.   <CustomColumn>
  25.     <Name>FMT</Name>
  26.     <DataType>0</DataType>
  27.     <Value>None</Value>
  28.   </CustomColumn>
  29. </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

  1. Customer    Product    Name   DataType   Value
  2. -------------------------------------------------
  3. 69731       157        MOI    0          10% max
  4. 69731       157        PRO    0          26% min
  5. 69731       157        FAT    0          
  6. 69731       157        WAT    0          
  7. 69731       157        FMT    0

I can create this dataset with the following:

  1. select
  2.   customer
  3.   ,product 
  4.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Name)[1]', 'varchar(max)')
  5.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/DataType)[1]', 'varchar(max)')
  6.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Value)[1]', 'varchar(max)')
  7. from
  8.   CustomerCrossReferences
  9. UNION ALL
  10. select
  11.   customer
  12.   ,product 
  13.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Name)[2]', 'varchar(max)')
  14.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/DataType)[2]', 'varchar(max)')
  15.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Value)[2]', 'varchar(max)')
  16. from
  17.   CustomerCrossReferences
  18. UNION ALL
  19. select
  20.   customer
  21.   ,product 
  22.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Name)[3]', 'varchar(max)')
  23.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/DataType)[3]', 'varchar(max)')
  24.   ,customColumns.value('(/CustomColumnsCollection/CustomColumn/Value)[3]', 'varchar(max)')
  25. from
  26.   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

答案1

得分: 3

你可以使用XQuery的nodes()方法以行格式获取你的XML数据，类似以下方式：

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

结合value()方法，你可以检索CustomColumn的各个元素。

英文:

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

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

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

huangapple
  • 本文由 发表于 2023年5月22日 01:25:48
  • 转载请务必保留本文链接：https://go.coder-hub.com/76301108.html
  • reporting-services
  • sql-server
  • t-sql
  • xml
  • xquery
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定