How to not insert xmlns in SQL Server?

Question

I need to add some XML elements into an XML column in SQL Server.

Here's a simplified example of my code:

DECLARE @temp XML = '<Rate>' + CONVERT(VARCHAR(20), @RateAmt, 1) + '</Rate>'

UPDATE [TableName]
SET [XMLValue].modify('declare namespace ns="http://www.mycompany.com/schema";insert sql:variable("@temp") as last into (/ns:Element1/ns:Element2)[1]') 
WHERE id = @Id

Here's the output:

<Rate xmlns="">12.00</Rate>

So, the code is working, however, how do I remove the xmlns="" attribute?

Mike · Accepted Answer · 2017-01-09 19:16:14Z

1

Why are you inserting a namespace if you don't want one in the xml?

DECLARE @RateAmt decimal(9,2) = 12.00
DECLARE @temp XML = '<Rate>' + CONVERT(VARCHAR(20), @RateAmt, 1) + '</Rate>'

DECLARE @tempTable TABLE
(
Column1 Xml
)

INSERT INTO @tempTable(Column1)
SELECT @temp

OR

UPDATE @tempTable
SET Column1 = (SELECT @temp)


SELECT * FROM @tempTable

<Rate>12.00</Rate>

(1 row(s) affected)

answered Jan 9, 2017 at 19:16

Mike

5502 silver badges17 bronze badges

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

2 Comments

Gottfried Lesigang Over a year ago

Good answer, voted this up, but one hint: You should avoid to create XML via string concatenation... (although this is coming from OP's post). And further more: Your UPDATE/INSERT would not work for the OP as he tries to insert this <Rate>-element into existing XML...

Mike Over a year ago

The concatenation of strings to create XML was from the initial post, not my design.

Gottfried Lesigang · Accepted Answer · 2017-01-10 07:56:56Z

There is an accepted answer already (especially concerning your namespace issue), great, just some hints:

There are very rare situation where one should create XML via string concatenation... Especially in connection with strings (special characters!), numbers (format!) and date/time values (culture and format!) it is much better to rely on the implicit translations using SELECT ... FOR XML ...

DECLARE @RateAmt DECIMAL(12,4)=12.0;

This is possible, but not good:

DECLARE @temp XML = '<Rate>' + CONVERT(VARCHAR(20), @RateAmt, 1) +'</Rate>'

Better try this

DECLARE @temp XML=(SELECT @RateAmt FOR XML PATH('Rate'));

Your attempt to insert this into existing XML can be done the way you do it already (create the XML-node externally and insert it as-is), it might be easier to insert the plain value:

DECLARE @tbl TABLE(ID INT IDENTITY,XMLValue XML);
INSERT INTO @tbl VALUES
 (N'<Element1><Element2><test>FirstTest</test></Element2></Element1>')
,(N'<Element1><Element2><test>Second</test></Element2></Element1>');

--ID=1: Insert the way you do it:

UPDATE @tbl
SET [XMLValue].modify('insert sql:variable("@temp") as last into (/Element1/Element2)[1]') 
WHERE id = 1

--ID=2: Insert the value of @RateAmt directly

SET @RateAmt=100.00;
UPDATE @tbl
SET [XMLValue].modify('insert <Rate>{sql:variable("@RateAmt")}</Rate> as last into (/Element1/Element2)[1]') 
WHERE id = 2

This is Result ID=1

<Element1>
  <Element2>
    <test>FirstTest</test>
    <Rate>12.0000</Rate>
  </Element2>
</Element1>

And ID=2

<Element1>
  <Element2>
    <test>Second</test>
    <Rate>100</Rate>
  </Element2>
</Element1>

Collectives™ on Stack Overflow

How to not insert xmlns in SQL Server?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related