Insert XML Node into a SQL column in a table
<p>I have an XML file that I am passing into a stored procedure. </p>

<p>I also have a table. The table has the columns VehicleReg | XML | ProcessedDate</p>

<p>My XML comes in like so: </p>

<pre><code>&lt;vehicles&gt; 
  &lt;vehicle&gt;
    &lt;vehiclereg&gt;AB12CBE&lt;/vehiclereg&gt;
    &lt;anotherprop&gt;BLAH&lt;/anotherprop&gt;
  &lt;/vehicle&gt;
  &lt;vehicle&gt;
    &lt;vehiclereg&gt;AB12CBE&lt;/vehiclereg&gt;
    &lt;anotherprop&gt;BLAH&lt;/anotherprop&gt;
  &lt;/vehicle&gt;
&lt;/vehicles&gt;
</code></pre>

<p>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). </p>

<p>I was working on something like below but had no luck:</p>

<pre><code>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)
</code></pre>

<p>I was wondering if someone could point me in the right direction? </p>

<p>Regards</p>