Insert XML Node into SQL column in table

Question

I have an XML file that I pass to a stored procedure.

I also have a table. The table shows the columns VehicleReg | XML | Processeddate

My XML looks like this:

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

What I need to do is read the xml and insert the carlereg line and the full xml line of the vehicle in each line (dateprocessed - getdate (), so no problem).

I worked on something like below, but no luck:

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)

I was wondering if anyone could point me in the right direction?

Hi

Answer 1

Full request according to your desire:

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)

Answer 2

Just remember that XML is case sensitive. You had:

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)

:

Answer 3

You can get XML XML.query(), in this case query('.')on vehicles/vehiclenode, to get internal 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;

Result:

+------------+------------------------------------------------------------------------------------+-------------------------+
| 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 |
+------------+------------------------------------------------------------------------------------+-------------------------+