Trouble Converting XML data to SQL Relational Table Data with XQuery

• Pat_B

  SSCommitted

  Points: 1526

  More actions

  May 22, 2008 at 2:55 pm

  #207584

  I have data stored in a SQL 2005 database in the XML data type. I am able to extract values for this data using XQuery (see below), but I don't know how to store the data into relational tables so I can report and perform calculations on it.

  /*

  -- Partial data for one row of XML data in a column called "xml":

  -- Query to extract Client_Name and Contact1_Name values

  select description,

  xml.value('(Data/Client_Name/@value)[1]','varchar(200)') as client,

  xml.value('(Data/Contact1_Name/@value)[1]','varchar(200)') as contact1

  from FileStore

  where description = 'Invoice_10013'

  */

  How can I take the extracted values and store them in a table now?

• SwePeso

  SSC-Dedicated

  Points: 39725

  More actions

  May 23, 2008 at 1:54 am

  #819520

  INSERT TargetTable (description, client, contact)

  select description,

  xml.value('(Data/Client_Name/@value)[1]','varchar(200)') as client,

  xml.value('(Data/Contact1_Name/@value)[1]','varchar(200)') as contact1

  from FileStore

  where description = 'Invoice_10013'

  ---

  N 56°04'39.16"
  E 12°55'05.25"