Procedure with a variable

• steppe

  Newbie

  Points: 2

  More actions

  November 16, 2011 at 12:59 pm

  #251919

  I've got this Procedure:

  it runs well, but when i want to get the xml-File in the Bulk as a variable i don't get it run... 🙁

  and I don't know how I can solve this with an EXEC???

  Any ideas??

  Thanks!

  Steppe

  DECLARE @docHandleint

  DECLARE @xmlDocumentxml -- or xml type

  SET @xmlDocument =(SELECT * FROM OPENROWSET(

  BULK 'C:\Users\WST\Desktop\Job_Revit\Import\ATPSheet1.xml',

  SINGLE_BLOB) AS x)

  EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

  -- Use OPENXML to provide rowset consisting of customer data.

  INSERT PLA_ELEMENT

  SELECT *

  FROM OPENXML(@docHandle, '/FILE_EXPORT/PLA_ELEMENT',2)

  WITH PLA_ELEMENT

  --Use OPENXML to provide rowset consisting of order data.

  EXEC sp_xml_removedocument @docHandle

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  November 17, 2011 at 7:31 am

  #1409195

  This should do (untested):

  DECLARE @docHandle int

  DECLARE @xmlDocument xml

  DECLARE @xmlFilePath nvarchar(max)

  DECLARE @sql nvarchar(max)

  SET @sql = '

  SET @xmlDocument = (

  SELECT * FROM OPENROWSET(

  BULK ''' + @xmlFilePath + ''',

  SINGLE_BLOB) AS x)'

  EXEC sp_executesql @sql, N'@xmlDocument xml OUTPUT', @xmlDocument OUTPUT

  EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

  -- Use OPENXML to provide rowset consisting of customer data.

  INSERT PLA_ELEMENT

  SELECT *

  FROM OPENXML(@docHandle, '/FILE_EXPORT/PLA_ELEMENT',2)

  WITH PLA_ELEMENT

  --Use OPENXML to provide rowset consisting of order data.

  EXEC sp_xml_removedocument @docHandle

  -- Gianluca Sartori

• steppe

  Newbie

  Points: 2

  More actions

  November 17, 2011 at 2:34 pm

  #1409554

  Thanks... 🙂

  I always must have some small mistakes in my version with this execution... but this way it runs!!!