我有一個帶有 XML 列的客戶訂單表，它包含他們的訂單詳細資訊。我需要將 XML 轉儲到具有正確架構的表中
表架構：
CREATE TABLE [dbo].[CustomerOrder]
(
[Id] INT NOT NULL IDENTITY(1,1),
[CustomerName] NVARCHAR(30),
[OrderDate] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
[Items] XML
CONSTRAINT [PK_dbo_CustomerOrder] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
樣本資料：
INSERT INTO [dbo].[CustomerOrder]([CustomerName], [Items])
VALUES (N'John', N'<Orders>
<Order>
<Item>
<Id>8D267E8E-2B1E-4FBF-8EE3-52D03D25A4F5</Id>
<Name>Tape</Name>
<Quantity>1</Quantity>
<Rate>25.00</Rate>
<NetAmount>25.00</NetAmount>
</Item>
<Item>
<Id>B661043F-3299-41CD-84A7-B16E824B3C8D</Id>
<Name>Rope</Name>
<Quantity>2</Quantity>
<Rate>20.00</Rate>
<NetAmount>40.00</NetAmount>
</Item>
</Order>
</Orders>')
INSERT INTO [dbo].[CustomerOrder]([CustomerName], [Items])
VALUES (N'Peter', N'<Orders>
<Order>
<Item>
<Id>B661043F-3299-41CD-84A7-B16E824B3C8D</Id>
<Name>Rope</Name>
<Quantity>5</Quantity>
<Rate>20.00</Rate>
<NetAmount>100.00</NetAmount>
</Item>
</Order>
</Orders>')
我嘗試將上述資料轉儲到具有以下表結構的臨時表中
CustomerOrderId Name OrderDate ItemName Quantity Rate NetAmount
____________________________________________________________________________________________________
1 John 2021-08-11 06:05:42.020 Tape 1 25.00 25.00
1 John 2021-09-10 08:11:30.490 Rope 2 20.00 40.00
2 Peter 2021-10-05 09:30:10.850 Rope 5 20.00 100.00
請指導我如何從上表中實作這一目標 [dbo].[CustomerOrder]
uj5u.com熱心網友回復：
您可以XML像這樣從資料中獲取單個值：
SELECT
CustomerOrderId = Id,
Name = CustomerName,
OrderDate,
ItemName = xc.value('(Name/text())[1]', 'varchar(50)'),
Quantity = xc.value('(Quantity/text())[1]', 'int'),
Rate = xc.value('(Rate/text())[1]', 'decimal(20,2)'),
NetAmount = xc.value('(NetAmount/text())[1]', 'decimal(20,2)')
FROM
dbo.CustomerOrder
CROSS APPLY
Items.nodes('/Orders/Order/Item') as XT(XC)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/316702.html
標籤：sql sql-server xml sqlxml
下一篇：可繪制檔案在暗模式下不會改變顏色