How to insert dynamic node in xml? sql server

Question

I have a table A. I want to insert column into xml.

I know '@x.modify('insert <a>{sql:column("XColumn")}</a>' into /') from A' is ok.

But how to insert dynamic node in xml?

'@x.modify('insert <{sql:column("XColumn")}>1</{sql:column("XColumn")}>' into /') from A' is error.

I can't find any workaround in docs.

Answer 1

So you will have to construct the XML you are inserting in SQL. For example:

DECLARE @NewXML XML

-- I am sure there is a cleaner way to do this... / Using a template
SET @NewXML = CONVERT(XML, '<' + @ElementName + '>' + @Value + '</' + @ElementName + '>')

@XML.modify('insert sql:variable("@NewXML") into (/root)[1]')

Hope it's what you're looking for!