在表的SQL列中插入XML节点

Question

我有一个XML文件，我正在将其传递到存储过程中。

我还有一张表。该表有三列：VehicleReg | XML | ProcessedDate。

我的XML文件如下：

<vehicles> 
  <vehicle>
    <vehiclereg>AB12CBE</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
  <vehicle>
    <vehiclereg>AB12CBE</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
</vehicles>

我需要做的是读取 XML，然后将 vehiclereg 和完整的车辆 XML 字符串插入每一行（dateprocessed 是一个 getdate()，所以不是问题）。

我尝试了下面这样的代码，但没有成功：

DECLARE @XmlData XML
Set @XmlData = EXAMPLE XML
SELECT T.Vehicle.value('(vehiclereg)[1]', 'NVARCHAR(10)') AS vehiclereg,
       T.Vehicle.value('.', 'NVARCHAR(MAX)'),
       GETDATE()
FROM @XmlData.nodes('Vehicles/Vehicle') AS T(Vehicle)

我想知道是否有人能指点我正确的方向？

问候

- Andrew Berry

---

你的 XML 中没有 Registration 元素。另外，你想要将整个 XML 存储在每一行中吗？还是只存储相应的车辆元素？ - TT.

谢谢，我已经更正了我的OP，但问题更多地是选择包括标签在内的完整车辆字符串，因为我正在使用它来生成校验和，然后使用此校验和检测导入到数据库中的更新。 - Andrew Berry

你的示例 XML 中有多个具有相同 vehiclereg 元素值的 vehicle 元素吗？AB12CBE 出现了两次。 - TT.

这只是示例数据，实际的注册信息会有所不同。 - Andrew Berry

Answer 1

只需要记住XML区分大小写。你之前的代码是：

FROM @XmlData.nodes('Vehicles/Vehicle') AS T(Vehicle)

但是你应该具备以下能力：

FROM @XmlData.nodes('/vehicles/vehicle') AS T(Vehicle)

如TT所指出的那样，没有名为“Registration”的列。

这应该就可以了。

DECLARE @XmlData XML
Set @XmlData = '<vehicles> 
  <vehicle>
    <vehiclereg>AB12CBE</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
  <vehicle>
    <vehiclereg>AB12CBE</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
</vehicles>'
SELECT  Vehicle.value('(vehiclereg)[1]', 'NVARCHAR(10)') AS vehiclereg,
        Vehicle.value('.', 'NVARCHAR(MAX)'),
       GETDATE()
FROM @XmlData.nodes('/vehicles/vehicle') AS T(Vehicle)

结果：

这将返回XML：

DECLARE @XmlData XML
Set @XmlData = '<vehicles> 
  <vehicle>
    <vehiclereg>AB12CBE</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
  <vehicle>
    <vehiclereg>AB12CBE</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
</vehicles>'
SELECT T.Vehicle.value('(vehiclereg)[1]', 'NVARCHAR(10)') AS vehiclereg,
       T.Vehicle.query('.'),
       GETDATE()
FROM @XmlData.nodes('vehicles/vehicle') AS T(Vehicle)

结果：

该图片无法翻译，但是您可以通过点击链接查看图片。

- Fuzzy

Answer 2

您想要的完整查询：

DECLARE @XmlData XML
Set @XmlData = '<vehicles> 
  <vehicle>
    <vehiclereg>AB12CBE</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
  <vehicle>
    <vehiclereg>AB12CBE</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
</vehicles>'
SELECT T.Vehicle.value('./vehiclereg[1]', 'NVARCHAR(10)') AS vehiclereg,
       T.Vehicle.query('.'),
       GETDATE()
FROM @XmlData.nodes('/vehicles/vehicle') AS T(Vehicle)

- Ruslan K.

---

啊，我使用的是值而不是查询，谢谢。 - Andrew Berry

Answer 3

您可以通过XML.query()获取XML，这里使用vehicles/vehicle节点上的query('.')来获取内部XML。

DECLARE @x XML=
'<vehicles> 
  <vehicle>
    <vehiclereg>AB12CBE</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
  <vehicle>
    <vehiclereg>AB13QQT</vehiclereg>
    <anotherprop>BLAH</anotherprop>
  </vehicle>
</vehicles>';

DECLARE @t TABLE(VehicleReg VARCHAR(128),[XML] XML,ProcessedDate DATETIME);

INSERT INTO @t(VehicleReg,[XML],ProcessedDate)
SELECT VehicleReg=n.v.value('(vehiclereg)[1]','VARCHAR(128)'),
       [XML]=n.v.query('.'),
       ProcessedDate=GETDATE()
FROM @x.nodes('vehicles/vehicle') AS n(v);

SELECT * FROM @t;

结果：

+------------+------------------------------------------------------------------------------------+-------------------------+
| VehicleReg |                                XML                                                 |      ProcessedDate      |
+------------+------------------------------------------------------------------------------------+-------------------------+
| AB12CBE    | <vehicle><vehiclereg>AB12CBE</vehiclereg><anotherprop>BLAH</anotherprop></vehicle> | 2016-03-01 15:21:37.640 |
| AB13QQT    | <vehicle><vehiclereg>AB13QQT</vehiclereg><anotherprop>BLAH</anotherprop></vehicle> | 2016-03-01 15:21:37.640 |
+------------+------------------------------------------------------------------------------------+-------------------------+

- TT.