accessing value from SQL parameter to use it for an XML attribute

Question

I am trying to build an xml by using the parameter value for the attribute.

Declare @price int =100

Declare @xml xml = '<Product DiscountPrice= "{sql:variable("@price")}"   />'

Select @xml

I get the following error.

Msg 9410, Level 16, State 1,
XML parsing: whitespace expected'

What am I missing here?

nathan_jr · Accepted Answer · 2014-03-10 17:02:36Z

2

I believe sql:variable is only used within .query() calls. This is how you can create a snippet of xml using your variable:

Declare @price int =100
declare @xml xml;

set @xml = (select [@DiscountPrice] = @price for xml path('Product'));

Select @xml

answered Mar 10, 2014 at 17:02

nathan_jr

9,3023 gold badges43 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1