Help needed reading xml files in T-SQL

• RTaylor2208

  SSChampion

  Points: 13298

  More actions

  May 18, 2010 at 5:47 am

  #232610

  I have the following code which works to create a variable holding xml from a file.

  DECLARE @xmlDoc XML

  SET @xmlDoc = ( SELECT * FROM OPENROWSET ( BULK 'C:\WIP\XML_Example\1_TestData.xml', SINGLE_CLOB ) AS xmlData)

  But I need to paramaterise the file being supplied as I have about 1000 files a day comming into the directory.

  I was going to use a loop to load the files one by one into a table. The problem I have is if I try and replace the explicit filename with a variable the syntax won't accept it.

  DECLARE @xmlDoc XML

  DECLARE @FileName varchar(200)

  SET @fileName = 'C:\WIP\XML_Example\1_TestData.xml'

  SET @xmlDoc = ( SELECT * FROM OPENROWSET ( BULK @fileName, SINGLE_CLOB ) AS xmlData)

  I tried using dynamic sql to get roung the problem which outputs what I want to screen but is not populating the variable @xmlDoc:

  DECLARE @xmlDoc XML

  DECLARE @sqlstmt nvarchar(max)

  SET @sqlstmt= 'SELECT * FROM OPENROWSET ( BULK ''' + @filename + ''', SINGLE_CLOB )AS xmlData'

  EXEC sp_executesql @sqlstmt, N'@xmlDoc XML output', @xmlDoc output

  Any ideas as if I can get the second solution working the rest of my soution works.

  Thanks in advance.

  MCITP SQL 2005, MCSA SQL 2012

• Mike01

  SSChampion

  Points: 11395

  More actions

  May 18, 2010 at 9:33 am

  #1168895

  I was able to do this using dynamic syntax and a temp table. Here's an example

  declare @filname nvarchar(50), @vs_Syntax nvarchar(max)

  Create table #vt_Results (XMLPath nvarchar(200))

  set @filename = 'c:\File.Config'

  set @vs_Syntax = '

  declare @x xml,

  @XMLhandle int

  select @x = convert(xml, BulkColumn, 2)

  FROM OPENROWSET(BULK ''' + @filename + ''', SINGLE_BLOB) AS Document

  EXEC sp_xml_preparedocument @XMLHandle OUTPUT, @x

  insert into #vt_Results

  select URL from OPENXML (@XmlHandle, ''/DTSConfiguration/Application'', 2)

  WITH (ID varchar(100) ''@ID'',

  URL varchar(200) ''URL'')

  EXEC sp_xml_removedocument @XMLHandle'

  exec sp_executesql @vs_Syntax

  select @vs_URL = XMLPath from #vt_results

  drop table #vt_Results

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• RTaylor2208

  SSChampion

  Points: 13298

  More actions

  May 19, 2010 at 1:59 am

  #1169267

  Thanks I managed to get it working using a similar solution:

  DECLARE @results table (result xml)

  --Build the Dynamic SQL Statement to get the data from the xml file

  SET @sqlstmt= 'SELECT * FROM OPENROWSET ( BULK ''' + @filename + ''', SINGLE_CLOB )AS xmlData'

  -- Insert the results of the dynamic SQL Statement into the temporary table variable.

  INSERT INTO @results EXEC (@sqlstmt)

  DECLARE @xmlDoc XML

  SELECT @xmlDoc = result FROM @results

  MCITP SQL 2005, MCSA SQL 2012