Блог

Главная - Вопросы по программированию - Сбросьте столбец XML в таблицу с соответствующей схемой таблицы из другой таблицы в SQL Server

Сбросьте столбец XML в таблицу с соответствующей схемой таблицы из другой таблицы в SQL Server

#sql #sql-server #xml #sqlxml

Вопрос:

У меня есть таблица заказов клиентов с 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]

Комментарии:

1. Привет. Вы упомянули, что пытались сбросить данные во временную таблицу. Как далеко вы продвинулись?

2. @TT. Я не получаю никаких решений и имею некоторый код ошибки сборки. Не могли бы вы, пожалуйста, помочь.

Ответ №1:

Вы можете извлечь отдельные значения из своих 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)

Комментарии:

1. В идеале вы всегда должны добавлять text() в свой .value XQuery, потому что он более производителен dba.stackexchange.com/a/207384/220697

2. @Charlieface: спасибо — я все время забываю об этом ….. но вы, конечно, на 100% правы! Обновил свой ответ

Метки: