SQL Server XQUERY - modify using multiple variables

kuhbmx9i 于 2022-12-10 发布在其他

关注(0)|答案(2)|浏览(191)

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.

sql-server

来源：https://stackoverflow.com/questions/74707796/xquery-modify-using-multiple-variables

2条答案

按热度按时间

laik7k3q1#

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.

赞(0）回复(0）举报 2022-12-10

ctzwtxfj2#

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.

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server XQUERY - modify using multiple variables

2条答案

相关问题

热门标签

最新问答