Copy xml node value to a column in another table

• bzoom100

  Ten Centuries

  Points: 1092

  More actions

  January 12, 2017 at 9:41 am

  #316915

  Good morning. I would like to copy one node from an xml data column to another non-xml column.
  This first set of code below pulls a single node of data from an Xml column.
  
  Select XmlDataField.value('(//*[local-name()="Description"])[1]', 'nvarchar(max)') 
    From tblDataPermit
     Where Type = '6'
  

  I would like to copy that piece of the xml value to a column in another table, joined by ApplicationNumber.
  Is this possible?
  In my draft code below that xml node is represented by XmlDataFieldNode
  
Update tblDataActions
SET tblDataActions.TextNotes = tblDataPermit.XmlDataFieldNode
FROM dbo.tblDataActions
JOIN dbo.tblDataPermit ON tblDataActions.ApplicationNumber = tblDataPermit.ApplicationNumber
WHERE tblDataActions.Actions = 'Inspection - Violation'


• Thom A

  SSC Guru

  Points: 99130

  More actions

  January 13, 2017 at 5:51 am

  #1922528

  This is a bit of guess work based on data you provided in prior posts, and the above but something like this should work:
  --WITH XMLNAMESPACES(DEFAULT 'MgmsSchema/Business_Information_Detail_Entry') --Not sure if you need this this time, you did before.*/
UPDATE tblDataActions
SET tblDataActions.DataActions = p.value('ACtion[1]', 'VARCHAR(8000)')
FROM tblDataActions DA
     INNER JOIN tblDataPermit DP ON DA.ApplicationNumber = DP.ApplicationNumber
     CROSS APPLY DA.XmlDataField.nodes('/XmlDataField') t(p)
WHERE DA.Actions = 'Inspection - Violation';

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• bzoom100

  Ten Centuries

  Points: 1092

  More actions

  January 13, 2017 at 8:58 am

  #1922596

  Will this just copy the node "Description"?
  Thank you for your help.

• Thom A

  SSC Guru

  Points: 99130

  More actions

  January 13, 2017 at 9:08 am

  #1922603

  bzoom100 - Friday, January 13, 2017 8:58 AM

  Will this just copy the node "Description"?
  Thank you for your help.

  The code I gave you would just copy the node Action. if you want to copy other values to other columns, you would need to add them in. if you want multiple values to be moved over, you would need to concatenate the values.

  If you need more specific detail, I'll need more information. As i said, the above is guesswork.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk