I have an XML variable containing a table. This table has a number of columns and one row. In the first two columns, I need to update their values with the result from a stored procedure - inserting a value if it is empty and updating the value if it already exists.

This is what I have so far. Please note that the names/structure of the elements in the table are true to how they will be in reality (i.e., all rows are called "Row" and all columns are called "Column"). This is due to a constraint of the program outputting the XML table.

-- Declare xml variable. Either column could be empty or populated. They are shown empty here.
DECLARE @x xml = N'<?xml version="1.0" encoding="utf-16"?>
<Table>
  <Row>
    <Column></Column>
    <Column></Column>
    <Column>some data</Column>
    <Column>some more data</Column>
  </Row>
</Table>'

-- View xml table prior to modifications
SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
      ,tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
      ,tbl.col.value('Column[3]/text()[1]', 'varchar(50)') 'data'
      ,tbl.col.value('Column[4]/text()[1]', 'varchar(50)') 'more data'
FROM @x.nodes('//Table/Row') tbl(col)

-- Declare new id and date variables. This will come from a stored procedure. I've made it static to simplify the example.
DECLARE @id int = 24
DECLARE @date datetime2(2) = sysutcdatetime()

-- If id is empty, insert the id value. Otherwise, update the id value.
IF (SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
    FROM @x.nodes('//Table/Row') tbl(col)) IS NULL

    SET @x.modify('insert text{sql:variable("@id")} into (//Table/Row/Column)[1]')

ELSE
    
    SET @x.modify('replace value of (//Table/Row/Column/text())[1] with sql:variable("@id")')

-- If date is empty, insert the date value. Otherwise, update the date value
IF (SELECT tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
    FROM @x.nodes('//Table/Row') tbl(col)) IS NULL

    SET @x.modify('insert text{sql:variable("@date")} into (//Table/Row/Column)[2]')

ELSE
    
    SET @x.modify('replace value of (//Table/Row/Column/text())[2] with sql:variable("@date")')

-- View xml table after modifications
SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
      ,tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
      ,tbl.col.value('Column[3]/text()[1]', 'varchar(50)') 'data'
      ,tbl.col.value('Column[4]/text()[1]', 'varchar(50)') 'more data'
FROM @x.nodes('//Table/Row') tbl(col)

This seems to work; however, I'm wondering if there is a way to write the condition (i.e., if empty, insert; else, replace value) within the modify function itself? In other words, can I avoid the SQL IF...ELSE statements and use one SET statement instead?

Also, is there a way to update the two columns in the same modify function or does that have to be separate?

Thanks in advance!

EDIT Altered sample code to demonstrate that the table may have additional columns and to clarify the format of the XML table.

I have an XML variable containing a table. This table has a number of columns and one row. In the first two columns, I need to update their values with the result from a stored procedure - inserting a value if it is empty and updating the value if it already exists.

This is what I have so far. Please note that the names/structure of the elements in the table are true to how they will be in reality (i.e., all rows are called "Row" and all columns are called "Column"). This is due to a constraint of the program outputting the XML table.

-- Declare xml variable. Either column could be empty or populated. They are shown empty here.
DECLARE @x xml = N'<?xml version="1.0" encoding="utf-16"?>
<Table>
  <Row>
    <Column></Column>
    <Column></Column>
    <Column>some data</Column>
    <Column>some more data</Column>
  </Row>
</Table>'

-- View xml table prior to modifications
SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
      ,tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
      ,tbl.col.value('Column[3]/text()[1]', 'varchar(50)') 'data'
      ,tbl.col.value('Column[4]/text()[1]', 'varchar(50)') 'more data'
FROM @x.nodes('//Table/Row') tbl(col)

-- Declare new id and date variables. This will come from a stored procedure. I've made it static to simplify the example.
DECLARE @id int = 24
DECLARE @date datetime2(2) = sysutcdatetime()

-- If id is empty, insert the id value. Otherwise, update the id value.
IF (SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
    FROM @x.nodes('//Table/Row') tbl(col)) IS NULL

    SET @x.modify('insert text{sql:variable("@id")} into (//Table/Row/Column)[1]')

ELSE
    
    SET @x.modify('replace value of (//Table/Row/Column/text())[1] with sql:variable("@id")')

-- If date is empty, insert the date value. Otherwise, update the date value
IF (SELECT tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
    FROM @x.nodes('//Table/Row') tbl(col)) IS NULL

    SET @x.modify('insert text{sql:variable("@date")} into (//Table/Row/Column)[2]')

ELSE
    
    SET @x.modify('replace value of (//Table/Row/Column/text())[2] with sql:variable("@date")')

-- View xml table after modifications
SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
      ,tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
      ,tbl.col.value('Column[3]/text()[1]', 'varchar(50)') 'data'
      ,tbl.col.value('Column[4]/text()[1]', 'varchar(50)') 'more data'
FROM @x.nodes('//Table/Row') tbl(col)

This seems to work; however, I'm wondering if there is a way to write the condition (i.e., if empty, insert; else, replace value) within the modify function itself? In other words, can I avoid the SQL IF...ELSE statements and use one SET statement instead?

Also, is there a way to update the two columns in the same modify function or does that have to be separate?

Thanks in advance!

EDIT Altered sample code to demonstrate that the table may have additional columns and to clarify the format of the XML table.

• sql-server
• xml-dml
• xml.modify

1 Answer 1

Please try the following solution leveraging SQL Server XQuery and its FLWOR expression.

For this method it doesn't matter if XML values exist or not.

My understanding is that your XML sample is contrived, i.e. XML elements are all called Column. That's why in real life scenario you can use XPath local-name() function to distinguish XML elements by names instead of their position.

SQL #1

-- Declare xml variable. Either column could be empty or populated. They are shown empty here.
DECLARE @x xml = N'<?xml version="1.0" encoding="utf-16"?>
<Table>
  <Row>
    <Column></Column>
    <Column></Column>
  </Row>
</Table>';

-- View xml table prior to modifications
SELECT col.value('Column[1]/text()[1]', 'int') 'id'
      , col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
FROM @x.nodes('/Table/Row') tbl(col);

-- Declare new id and date variables.
DECLARE @id int = 24;
DECLARE @date datetime2(2) = sysutcdatetime();

SET @x = @x.query('<Table>
{
   for $y in /Table/Row
   return <Row>
   {
        for $z in $y/*
        return if ($z is $y/*[1]) then 
                element Column {sql:variable("@id")}
            else
            (
                element Column {sql:variable("@date")}
            )
    }
   </Row>
}
</Table>');

-- View xml table after modifications
SELECT col.value('Column[1]/text()[1]', 'int') 'id'
      , col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
FROM @x.nodes('/Table/Row') tbl(col);

Output

SQL #2

-- Declare xml variable. Either column could be empty or populated. They are shown empty here.
DECLARE @x xml = N'<?xml version="1.0" encoding="utf-16"?>
<Table>
  <Row>
    <ID></ID>
    <Date></Date>
    <State>FL</State>
    <City>Miami</City>
  </Row>
</Table>';

-- View xml table prior to modifications
SELECT col.value('(ID/text())[1]', 'int') AS [id]
      , col.value('(Date/text())[1]', 'datetime2(2)') AS [date]
      , col.value('(State/text())[1]', 'char(2)') AS [State]
      , col.value('(City/text())[1]', 'varchar(20)') AS [city]
FROM @x.nodes('/Table/Row') AS tbl(col);

-- Declare new id and date variables.
DECLARE @id int = 24;
DECLARE @date datetime2(2) = sysutcdatetime();

SET @x = @x.query('<Table><Row>
{
    for $z in /Table/Row/*
    return if (local-name($z)="ID") then 
            element ID {sql:variable("@id")}
        else if (local-name($z)="Date") then 
        (
            element Date {sql:variable("@date")}
        )
        else $z
}
</Row></Table>');

-- View xml table after modifications
SELECT  col.value('(ID/text())[1]', 'int') AS [id]
      , col.value('(Date/text())[1]', 'datetime2(2)') AS [date]
      , col.value('(State/text())[1]', 'char(2)') AS [State]
      , col.value('(City/text())[1]', 'varchar(20)') AS [city]
FROM @x.nodes('/Table/Row') AS tbl(col);

Output