Insert XML Node into a SQL column in a table

Question

I have an XML file that I am passing into a stored procedure.

I also have a table. The table has the columns VehicleReg | XML | ProcessedDate

My XML comes in like so:

<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 vehiclereg and the full vehicle xml string into each row (the dateprocessed is a getdate() so not a problem).

I was working on something like below but had 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 someone could point me in the right direction?

Regards

Answer 1

Full query as you want:

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 need to remember XML is case sensitive. You had:

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

but you should have had:

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

Also as TT pointed out there was no column named Registration

This should do it:

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)

Result:

This would return 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)

Result: