CODEWITHSUNDEEP
sqlsql-serverxmlxquery
Claudio
Claudio

Reputation: 250

Declaring XML in SQL Server issue

I have a T-SQL issue, I am trying to do the following and I receive an error message when I try to run the SELECT statement, saying that I didn't declare the @Xml variable, any help would be appreciated, thanks.

DECLARE @Xml XML = N'
 <products>
 <product id="1" name="Adjustable Race" />
 <product id="879" name="All-Purpose Bike Stand" />
 <product id="712" name="AWC Logo Cap" />
 <product id="19910" name="Cozonac" />
 </products>';

SELECT 
    xt.xc.value('@id', 'INT') AS ProductID,
    xt.xc.value('@name','NVARCHAR(50)') AS Name
FROM 
    @Xml.nodes('/products/product') AS xt(xc);

Upvotes: 3

Views: 14352

Answers (1)

Eralper
Eralper

Reputation: 6622

Declaring and directly assigning a value to a parameter is new. In previous versions of SQL Server, you should first declare and then as a seperate statement set the value of it. Perhaps you can try

DECLARE @Xml XML
SET @Xml = N'...'

Upvotes: 4

Related Questions