XML bulk insert with parameter

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 2005 Forums

Transact-SQL (2005)

XML bulk insert with parameter

Author

Topic

oveki.loki
Starting Member

1 Post

Posted - 2011-09-29 : 08:48:05

Hi

I have made xml import with bulk insert where xml file name is fixed

INSERT INTO XML_import(order_line_no, quantity, article_no)
SELECT X.orderline.query('order-line-no').value('.', 'INT'),
X.orderline.query('quantity').value('.', 'DECIMAL(18,5)'),
X.orderline.query('article-no').value('.', 'VARCHAR(30)')
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(
BULK 'c:\2\CRH395_20110913_829003.xml', SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY x.nodes('order/order-line') AS X(orderline)

Is any way to put variable instead fixed name?
When I have tried put variable I get error

then i made dynamics sql, but also doesn't work

declare @var_1 varchar(200)
declare @var_2 varchar(200)
declare @var_3 varchar(200)
declare @var_4 varchar(200)
declare @var_5 varchar(200)
declare @var_6 varchar(200)
declare @var_7 varchar(200)
declare @var_8 varchar(200)
declare @var_9 varchar(200)
declare @var_10 varchar(200)
declare @var_11 varchar(200)
declare @sql varchar(200)

set @var_1 = 'order-line-no'
set @var_2 = 'quantity'
set @var_3 = 'article-no'
set @var_4 = '.'
set @var_5 = 'INT'
set @var_6 = 'DECIMAL(18,5)'
set @var_7 = 'VARCHAR(50)'
set @var_8 = 'article-no'
set @var_9 = 'c:\2\CRH395_20110913_829003.xml'
set @var_10 = 'order/order-line'

set @sql = 'INSERT INTO XML_import(order_line_no, quantity, article_no) SELECT X.orderline.query('''+@var_1+''').value('''+@var_4+''', '''+@var_5 +'''), X.orderline.query('''+@var_2+''').value('''+@var_4+''', '''+@var_6+'''),X.orderline.query('''+@var_3+''').value('''+@var_4+''', '''+@var_7 +''') FROM (SELECT CAST(x AS XML) FROM OPENROWSET(BULK '''+@var_9+''', SINGLE_BLOB) AS T(x)) AS T(x) CROSS APPLY x.nodes('''+@var_10+''') AS X(orderline)'
exec(@sql)

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-29 : 12:28:06

first do print(@qry) instead of exec and check if generated query is correct by copying and executing in new window

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources