XQUERY - modify using multiple variables-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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.