shred XML Dynamic SQL

Question

Post reply

shred XML Dynamic SQL

APIJENA

SSC Veteran

Points: 296
More actions
January 28, 2010 at 7:56 am

#144558

I can not find the error in the following little query. please help !!
DECLARE @doc nvarchar (1000)
DECLARE @docXML xml
DECLARE @wholeFileName varchar(200)
set @wholeFileName='\\myPath\myFile.xml';
exec( 'SET ''' + @doc + ''' = (
SELECT convert(xml, BulkColumn, 2)FROM OPENROWSET (
BULK '''+ @wholeFileName +''' ,SINGLE_BLOB
) AS xmldata )
' ) ;
set @docXML=convert(xml, @doc );

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ohack SSCrazy Points: 2043 More actions · Answer 1

Hi,

You're not doing anything with the @doc variable; so even with SET CONCAT_NULL_YIELDS_NULL turned off you're getting:

SET '' = ( SELECT convert(xml, BulkColumn, 2)

FROM OPENROWSET ( BULK 'C:\Single.xml' ,SINGLE_BLOB ) AS xmldata )

(with it ON you'll just get NULL)

which obviously won't work. I would always use a variable to hold the dynamic sql - that way you can print or select it to see what you've actually built.

APIJENA SSC Veteran Points: 296 More actions · Answer 2

thanks, here is my final version, it's working perfectly !

DECLARE @wholeFileName varchar(200)

DECLARE @PathFileName varchar(200)

DECLARE @FName varchar(30)

DECLARE @RowCnt int

DECLARE @MaxRows int

SET @PathFileName ='\\my_path\'

DECLARE @tabfileNames TABLE ( rownum int IDENTITY (1, 1) Primary key NOT NULL, FName varchar(100) )

INSERT INTO @tabfileNames

exec master.dbo.xp_cmdshell 'dir \\my_path\*.xml/b'

SELECT @MaxRows=count(*) from @tabfileNames

SELECT @RowCnt = 1

WHILE @RowCnt <= @MaxRows

BEGIN

SELECT @FName = FName FROM @tabfileNames WHERE rownum = @RowCnt

SET @wholeFileName=@PathFileName + @FName;

exec('DECLARE @doc xml SET @doc = (

SELECT convert(xml, BulkColumn, 2)FROM OPENROWSET (

BULK ''' + @wholeFileName +''' ,SINGLE_BLOB

) AS xmldata )

insert into my_table

select * from (

select

TempXML.Node.value(''@doc-number'', ''varchar(200)'') as doc_number,

TempXML2.Node2.value(''(/name)[1]'', ''varchar(200)'') as name,

FROM @doc.nodes(''/my_element'') TempXML (Node)

CROSS APPLY TempXML.Node.nodes(''//my_other_element'') TempXML2 (Node2)

)a

' ) ;

SET @RowCnt = @RowCnt + 1

END /*while*/

END

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

thanks for posting your update.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events