October 25, 2013 at 9:46 am
Hi,
I have the code written to import my XML files fine. I was trying to turn it into a stored procedure, since I'm going to have to load a bunch of them for this client. I'm having trouble working out one bit of the code though:
select @sql = 'SELECT @x = P
FROM OPENROWSET (BULK ''\\p42\SurveyComputing\Sample\SampleRepository\[filename]'', SINGLE_BLOB) AS FMG(P)'
select @sql = replace(@sql, '[filename]', @filename)
print (@sql)
EXEC sp_executesql @sql, N'@x xml', @x;
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
I think there's a scope issue with @x, because sp_xml_preparedocument doesn't seem to do anything within the SP. Any pointers?
Thanks
October 25, 2013 at 12:22 pm
set @sql = N'SELECT @x = P
FROM OPENROWSET (BULK ''\\plaus42\SurveyComputing\Sample\SampleRepository\[filename]'', SINGLE_BLOB) AS FMG(P)'
set @sql = replace(@sql, '[filename]', @filename)
print (@sql)
--exec (@sql)
EXEC sp_executesql @sql, N'@x xml OUTPUT', @x = OUTPUT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
I read through BOL some, but OUTPUT doesn't seem to be doing what I need either, passing @x to sp_xml_preparedocument.
October 25, 2013 at 1:22 pm
Well, this fixed it. You can all go back to alert level pewter evening and return to your regularly scheduled rock n roll hoochie koo.
set @sql = N'SELECT @y = P
FROM OPENROWSET (BULK ''\\plaus42\SurveyComputing\Sample\SampleRepository\[filename]'', SINGLE_BLOB) AS FMG(P)'
set @sql = replace(@sql, '[filename]', @filename)
print (@sql)
--exec (@sql)
EXEC sp_executesql @sql, N'@y xml OUTPUT', @y = @x OUTPUT;
--select @x
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply