XML to SQL Server 2005

• carlos.tapadinhas

  Ten Centuries

  Points: 1058

  More actions

  September 23, 2008 at 3:15 am

  #103587

  Good Morning to All 🙂

  I need a little help here, i have a xml data file and i want to migrate that data to SQL Server 2005, does anyone already do this? Can you give some hints ?

  Best Regards

  Thanks.

• jacob sebastian

  SSChampion

  Points: 11822

  More actions

  September 23, 2008 at 4:18 am

  #875532

  I suppose, what you need is a way to load the XML data to a SQL Server table. You have a few options for that. You may use a TSQL insert query, XmlBulkLoad or an SSIS package.

  If it is a one time deal, I would suggest using the TSQL approach (this is what I guess will be better based on the minimum information provided). This approach involves loading the content from the file to an XML variable and then reading values and inserting to the required tables.

  To load the file to an XML variable, you can do the following (SQL Server 2005)

  DECLARE @xml XML

  SELECT @xml =CONVERT(XML, bulkcolumn, 2)

  FROM OPENROWSET(BULK 'C:\temp\file.xml', SINGLE_BLOB) AS x

  Once the XML data is loaded, you can use XQuery (based on the structure of your XML document, the XQuery expression may vary) to retrieve values from the XML variable and insert into a table.

  If you are new to XQuery, I would suggest reading the tutorials here: http://www.sqlserverandxml.com/2008/06/xquery-labs-collection-of-xquery-sample.html

  regards

  Jacob

  .

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 23, 2008 at 4:37 am

  #875537

  you must understand the Concept if XML before using it

  please go for BOL at

  http://msdn.microsoft.com/en-us/library/ms175160.aspx

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)