Dump the XML column into a table with proper table schema from another table in SQL Server-CodePudding

I'm having a customer order table with a XML column it consists their Order details. I need to dump the XML into a table with proper schema

Table Schema:

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]
)

Sample Data:

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>')

I tried to dump the above data into a temp table with the following table structure

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

Please guide me how to achieve this from the above table [dbo].[CustomerOrder]

CodePudding user response：

You can grab the individual values from your XML data like this:

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)