Consult xml file from sql server with OPENROWSET

Question

Consult xml file from sql server with OPENROWSET

Navigation

#1 by (1 votes)

1

I'm doing a query to an xml file from a store procedure and when placing the direct route it works. the problem is that the route I want to pass it by parameters and throws me error when placed as variable

DECLARE @XmlFile XML

SELECT @XmlFile = BulkColumn
FROM  OPENROWSET(BULK 'C:\example\mixml.xml', SINGLE_BLOB) x;

That's what I did and it works but when applying the change below and placing the path in a variable @file it says incorrect syntax.

DECLARE @XmlFile XML;
DECLARE @file varchar(max);

set @file = 'C:\example\mixml.xml';

SELECT @XmlFile = BulkColumn
FROM  OPENROWSET(BULK @file, SINGLE_BLOB) x;

Can someone help me with this?

sql sql-server xml file

asked by DeCo 18.12.2017 в 14:04

source

1 answer

How to use css3 variables, without problems of compatibility with Internet Explorer or other old browsers? missing error a parenthesis to the right ORA-00907

score 1 · Answer 1

Have you tried using a dynamic query? Something like this,

    DECLARE  @XmlFile   xml;
    DECLARE  @file      varchar(max);
    DECLARE  @Query     nvarchar(max)

    SELECT  @Query          =   'SELECT @varOut = BulkColumn
                        FROM  OPENROWSET(BULK ' + @file + ', SINGLE_BLOB) x;'

    EXECUTE sp_executesql @Query, N'@varOut xml out',@varOut=@XmlFile out

    SELECT @XmlFile