XQUERY - modify using multiple variables

Question

I am writing a stored procedure where I can add/modify any XML nodes with a supplied value on a XML column. @XML xml, @NODENAME nvarchar(100) = NULL, @NODEVALUE nvarchar(max) are being passed as parameters. The procedure should insert a value of @NODENAME as a node name with @NODEVALUE as a value into @XML string.

For example: if @XML is <XML></XML>, @NODENAME is 'DISTRICT' and @NODEVALUE is '123', after executing SP, you get <XML><DISTRICT>123</DISTRICT></XML>. Pretty straightforward.

I've got most of the use cases completed like:

when node exists with non-empty non-null value:

SET @XML.modify('replace value of (/XML/*[local-name()=sql:variable("@NodeName")]/text())[1] with (sql:variable("@myVar"))')

when node exists with an empty or null value:

SET @XML.modify('insert text{sql:variable("@myVar")} into (/XML/*[local-name()=sql:variable("@NodeName")])[1]')

But I can't figure out how to get the following use case:

when node does not exist

I have a hardcoded version that works:

SET @XML.modify('insert <DISTRICT>{sql:variable("@myVar")}</DISTRICT> into (/XML)[1]')

But I need to use something that does NOT hardcode the node (in this case DIVISION). I tried this but it does not work:

SET @XML.modify('insert <{sql:variable("@myVar")}>{sql:variable("@myVar")}</{sql:variable("@myVar")}> into (/XML)[1]') I get XQuery [modify()]: Syntax error near '{'

I've tried various different methods that I could find with no luck. How do I use a variable to represent inserting a NODE into XML? I use microsoft server.

Jorge Bugal · Accepted Answer · 2022-12-06 20:44:27Z

1

I also couldn't figure out a solution with just one operation for every case.

But one way of doing it would be first assuring the node exists, then proceeding to replace its value:

declare @xml xml, @NodeName sysname, @NodeValue nvarchar(max)
set @NodeName = N'DISTRICT'
set @NodeValue = N'New value'
set @xml = N'<XML></XML>'
-- Make sure the node exists
if @xml.exist(N'/XML/*[local-name(.)[1] = sql:variable("@NodeName")]') = 0
    declare @new_node xml = N'<'+@NodeName+N'></'+@NodeName+N'>'
    set @XML.modify(N'insert sql:variable("@new_node") into (/XML[1])')
-- And make sure it has something
SET @xml.modify(N'insert text{"X"} into (/XML/*[local-name()=sql:variable("@NodeName")])[1]')
-- Then replace it's contents
set @xml.modify(N'replace value of (/XML/*[local-name()=sql:variable("@NodeName")]/text())[1] with (sql:variable("@NodeValue"))')
print cast(@xml as nvarchar(max))

I've created this as a scalar function on this DB Fiddle, showing it works for every scenario described.

answered Dec 6, 2022 at 20:44

Jorge Bugal

4492 silver badges3 bronze badges

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

1 Comment

George Over a year ago

This is an alternative to my solution. Just a side note, your solution does not account for when @XML is completely empty or null (ie: SET @XML=CAST ('' AS XML)), but that can be mitigated with a simple if @xml is null OR @xml.exist(N'/XML') = 0 SET @xml='<XML></XML>'

George · Accepted Answer · 2022-12-06 19:48:26Z

0

I have a solution, not perfect, but still a solution. Instead of trying to have a solution in one line, I split it into two:

DECLARE @NewNode XML=CAST('<'+@NodeName+'>'+@myVar + '</' + @NodeName + '>' as XML)
SET @XML.modify('insert sql:variable("@NewNode") into (/XML)[1]')

I still don't know if it is possible to have a single line solution, if it is and someone posts it, I will mark that as an asnwer.

answered Dec 6, 2022 at 19:48

George

2,2123 gold badges25 silver badges37 bronze badges

Collectives™ on Stack Overflow

XQUERY - modify using multiple variables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related