CODEWITHSUNDEEP
xmlxquery
thebernardlim
thebernardlim

Reputation: 830

XQuery - How to enable sql:variable to keep decimal places

I am trying to keep the decimal places of whole numbers when values are retrieved from an XQuery sql:variable function.

Sample code:

DECLARE @variableName DECIMAL(16,2) = 123.00;
SET @XML.modify('replace value of (/Test/SubTest/text())[1] with sql:variable("@variableName")');

Result:

<Test>
     <SubTest> 123 </SubTest>
</Test>

Desired Result:

<Test>
     <SubTest> 123.00 </SubTest>
</Test>

What are the changes I need to make to get the desired result?

Thanks.

Upvotes: 0

Views: 330

Answers (2)

Jason
Jason

Reputation: 11

You need to convert decimal to nvarchar prior to use XQuery.

    DECLARE @xml xml = '<Test>A</Test>', @variableName DECIMAL(16,2) = 123.00, @str nvarchar(max)
SET @str = Convert(nvarchar(max), @variableName)
SET @XML.modify('replace value of (/Test/text())[1] with sql:variable("@str")');
SELECT @Xml, @str

Upvotes: 1

Ghislain Fourny
Ghislain Fourny

Reputation: 7279

The builtin function fn:format-number helps you formatting your numbers in the way you desire:

format-number(123, "000.00")

which gives:

replace value of (/Test/SubTest/text())[1]
with format-number(sql:variable("@variableName"), "000.00")

Upvotes: 0

Related Questions