xml file path - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

xml file path

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-01-05 : 18:05:04

Greetings

I have an SSIS package that calls an ETL sproc. I would like the SSIS package to pass the path of the xml file. How do I go about querying that xml file that is being passed as a parameter?


declare @xml_path nvarchar(max)
declare @xmldoc xml
SET @xml_path = '\\server\h$\Data1\xmlfiles\my.xml'

SELECT b.y.value('ID[1]', 'varchar(30)') ID,
	   b.y.value('Type[1]', 'varchar(30)') Type
  FROM @xml_path.nodes('/Path') AS a(x)
 CROSS APPLY x.nodes('Junk') b(y)

Thanks

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-01-07 : 14:28:53

for anyone interested this is the approach I took


 ( @xml_path nvarchar(255), 
   @xml_file_name nvarchar(50)
 )
AS 
DECLARE @xml_table TABLE(xml_data xml)

DECLARE @xmlDoc NVARCHAR(MAX), @handle INT, @sqlstmt NVARCHAR(MAX)

SET @sqlstmt= 'SELECT * FROM OPENROWSET ( BULK ''' + @xml_path + @xml_file_name + ''', SINGLE_CLOB ) AS XMLDATA'

INSERT INTO @xml_table EXEC (@sqlstmt)

SELECT @xmlDoc = CONVERT(NVARCHAR(max), xml_data ) FROM @xml_table

EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources