sql - 将具有相同 ItemNumber 的节点合并为一行
问题描述
一些供应商定期发布带有商品价格的 XML 文件。文件中有两种价格:目录价格(list Price)和折扣价(discount Price)
<prices>
<price>
<itemNumber>100</itemNumber>
<currency>PLN</currency>
<amount>2.98</amount>
<type>listPrice</type>
</price>
<price>
<itemNumber>100</itemNumber>
<currency>PLN</currency>
<amount>1.19</amount>
<type>discountPrice</type>
</price>
<price>
<itemNumber>101</itemNumber>
<currency>PLN</currency>
<amount>3.88</amount>
<type>listPrice</type>
</price>
<price>
<itemNumber>101</itemNumber>
<currency>PLN</currency>
<amount>2.76</amount>
<type>discountPrice</type>
</price>
<price>
<itemNumber>102</itemNumber>
<currency>PLN</currency>
<amount>4.55</amount>
<type>listPrice</type>
</price>
<price>
<itemNumber>102</itemNumber>
<currency>PLN</currency>
<amount>3.20</amount>
<type>discountPrice</type>
</price>
<price>
<itemNumber>103</itemNumber>
<currency>PLN</currency>
<amount>3.38</amount>
<type>listPrice</type>
</price>
<price>
<itemNumber>103</itemNumber>
<currency>PLN</currency>
<amount>2.90</amount>
<type>discountPrice</type>
</price>
<price>
<itemNumber>104</itemNumber>
<currency>PLN</currency>
<amount>2.98</amount>
<type>listPrice</type>
</price>
<price>
<itemNumber>104</itemNumber>
<currency>PLN</currency>
<amount>1.19</amount>
<type>discountPrice</type>
</price>
</prices>
目前我正在将 XML 文件上传到数据库,然后使用从 xml 表中获取的数据创建 tmp 表。
SELECT
@SupplierID AS SupplierID
, a.product.query('itemNumber').value('.', 'varchar(50)') AS SupplierCode
, a.product.query('amount').value('.', 'MONEY') AS PricePLN
, a.product.query('type').value('.', 'varchar(50)') AS NetPriceTypePLN
, GETDATE() AS CreateDate
INTO Prices_tmp
FROM ImportedXML x(nolock)
OUTER APPLY x.xmlData.nodes('prices/price') as a(product)
where
x.Fileid = 111
我正在努力解决的问题是如何读取 XML 以在一行中获取两个价格,例如:
ItemNumber | ListedPriceAmount | discountPriceAmount
100 | 2.98 | 1.19
101 | 3.88 | 2.76
102 | 4.55 | 3.20
103 | 3.38 | 2.90
请你帮助我好吗?我花了很多时间自己尝试。
解决方案
请尝试以下解决方案。
需要提及的要点:
- 将适当的 XML 分解为关系/矩形格式。
- 简单模拟 PIVOT 操作以实现所需的输出。
SQL
DECLARE @xml XML =
N'<prices>
<price>
<itemNumber>100</itemNumber>
<currency>PLN</currency>
<amount>2.98</amount>
<type>listPrice</type>
</price>
<price>
<itemNumber>100</itemNumber>
<currency>PLN</currency>
<amount>1.19</amount>
<type>discountPrice</type>
</price>
<price>
<itemNumber>101</itemNumber>
<currency>PLN</currency>
<amount>3.88</amount>
<type>listPrice</type>
</price>
<price>
<itemNumber>101</itemNumber>
<currency>PLN</currency>
<amount>2.76</amount>
<type>discountPrice</type>
</price>
<price>
<itemNumber>102</itemNumber>
<currency>PLN</currency>
<amount>4.55</amount>
<type>listPrice</type>
</price>
<price>
<itemNumber>102</itemNumber>
<currency>PLN</currency>
<amount>3.20</amount>
<type>discountPrice</type>
</price>
<price>
<itemNumber>103</itemNumber>
<currency>PLN</currency>
<amount>3.38</amount>
<type>listPrice</type>
</price>
<price>
<itemNumber>103</itemNumber>
<currency>PLN</currency>
<amount>2.90</amount>
<type>discountPrice</type>
</price>
<price>
<itemNumber>104</itemNumber>
<currency>PLN</currency>
<amount>2.98</amount>
<type>listPrice</type>
</price>
<price>
<itemNumber>104</itemNumber>
<currency>PLN</currency>
<amount>1.19</amount>
<type>discountPrice</type>
</price>
</prices>';
WITH rs AS
(
SELECT c.value('(itemNumber/text())[1]', 'varchar(50)') AS ItemNumber
, c.value('(amount/text())[1]', 'money') AS amount
, c.value('(type/text())[1]', 'varchar(50)') AS type
FROM @xml.nodes('/prices/price') AS t(c)
)
-- simulates PIVOT operation
SELECT DISTINCT
ItemNumber
, MAX(IIF(type = 'listPrice', amount, 0)) OVER (PARTITION BY ItemNumber) AS ListedPriceAmount
, MAX(IIF(type = 'discountPrice', amount, 0)) OVER (PARTITION BY ItemNumber) AS discountPriceAmount
FROM rs;
输出
+------------+-------------------+---------------------+
| ItemNumber | ListedPriceAmount | discountPriceAmount |
+------------+-------------------+---------------------+
| 100 | 2.98 | 1.19 |
| 101 | 3.88 | 2.76 |
| 102 | 4.55 | 3.20 |
| 103 | 3.38 | 2.90 |
| 104 | 2.98 | 1.19 |
+------------+-------------------+---------------------+
推荐阅读
- header - 头文件中的 const 定义,使用宏常量会导致链接器多重定义错误
- javascript - Chart.js - 2 不显示轴标签
- http - 如何在 xamarin android 中修复“不允许到 x 的明文 HTTP 流量”
- mongodb - Golang MongoDB insertMany 如果不存在
- c - 在 C 中的另一个 LL 中迭代嵌套的链表
- linux - 使用“apparmor + firejail”的问题,特别是在运行 cmd“firejail firefox”时,套接字错误?
- c# - Android Toast 实例视图返回 Null
- javascript - 根据布尔值构造一个常量数组
- python - 如何在流体模拟中添加暂停和开始按钮和一些滑块以实时更新而不重置?
- python - 需要以下参数