Home

Using variables in select and xml files

%3CLINGO-SUB%20id%3D%22lingo-sub-1254831%22%20slang%3D%22en-US%22%3EUsing%20variables%20in%20select%20and%20xml%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1254831%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20t-sql%20that%20read%20an%20xml%20file%20and%20store%20it%20into%20ms%20sql%202016%20database.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDECLARE%3CBR%20%2F%3E%40x%20xml%2C%3CBR%20%2F%3E%40%40XML_Location%20VARCHAR(255)%20%3D%20'FROM%20OPENROWSET%20(BULK%20''''%5C%5Cxmlsrv%5Cproxml%5C'%3CBR%20%2F%3E%40XML_File%20VARCHAR(255)%20%3D%20'GEN.xml'%2C%3CBR%20%2F%3E%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20%40x%20%3D%20P%3CBR%20%2F%3E--FROM%20OPENROWSET%20(BULK%20'%5C%5Cxmlsrv%5Cproxml%5CGEN.xml'%2C%20SINGLE_BLOB)%20AS%20Element(P)%3CBR%20%2F%3E%40XML_Location%20%26amp%3B%20%40XML_File%2C%20SINGLE_BLOB)%20AS%20Element(P)%3C%2FP%3E%3CP%3E.......%3C%2FP%3E%3CP%3E....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20script%20works%20if%20i%20use%20the%20from%20statement%20directly%2C%20but%20if%20I%20try%20to%20use%20declared%20variables%20it%20fails%20with%20message%3A%3C%2FP%3E%3CP%3EMsg%20102%2C%20Level%2015%2C%20State%201%2C%20Line%2016%3CBR%20%2F%3EIncorrect%20syntax%20near%20'%40XML_Location%20'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20am%20I%20doing%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBR%3C%2FP%3E%3CP%3EDan-Ketil%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1254831%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Evariables%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EXML%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Hi

 

I have created a t-sql that read an xml file and store it into ms sql 2016 database.

 

DECLARE
@x xml,
@@XML_Location VARCHAR(255) = 'FROM OPENROWSET (BULK ''''\\xmlsrv\proxml\'
@XML_File VARCHAR(255) = 'GEN.xml',
;

 

SELECT @x = P
--FROM OPENROWSET (BULK '\\xmlsrv\proxml\GEN.xml', SINGLE_BLOB) AS Element(P)
@XML_Location & @XML_File, SINGLE_BLOB) AS Element(P)

.......

....

 

The script works if i use the from statement directly, but if I try to use declared variables it fails with message:

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '@XML_Location '.

 

What am I doing wrong?

 

BR

Dan-Ketil