TSQL using modify to initialise a null column

Question

Using TSQL modify how can I initialise a null column with a root XML element, and use the value of a column to create/populate a nested element?

From what I can tell, there's no way to create a root XML node on a NULL column using modify.

Example table...

Id  Val  MyXML
1   2    NULL
2   5    NULL

Expected outcome...

1   2    <data><val>2</val></data>
2   5    <data><val>5</val></data>

The only way I can figure out doing it is the nasty string concatenation...

UPDATE MyTable
SET MyXML = '<data><val>' + CONVERT(VARCHAR(10),Val) + '</val></data>'

Or having two queries, the first to create the root, the second to add the element...

UPDATE MyTable
SET MyXML = '<data></data>'
UPDATE MyTable
SET MyXML.modify('insert <val>{sql:column("Val")}</val> into /data[1]')

Ideally I'd like something like this, but I cannot figure out if it's possible...

UPDATE MyTable
SET MyXML.modify('insert <data><val>{sql:column("Val")}</val></data>')

score 1 · Accepted Answer · answered Oct 31 '22 at 11:14

1

I don't know how you can use the modify to do this, but you should be able to do something like:

UPDATE MyTable
SET MyXML = (
    SELECT [Val] AS [val]
    FOR XML PATH('data'), TYPE
)
WHERE MyXML IS NULL

answered Oct 31 '22 at 11:14

TZHX

5,291
15
47
56

Clever, I like it. I've also just learnt that TYPE returns the XML as an actual XML type, rather than a string... I wonder how many times I've used `FOR XML` and allowed the server to convert to string and back to XML – freefaller Oct 31 '22 at 11:23
Would still be interested to know if the `modify` method has the ability. Gut feeling is it doesn't – freefaller Oct 31 '22 at 11:25

Yitzhak Khabinsky · Answer 2 · 2022-10-31T15:45:51.023

Because the MyXML column has initial NULL values, there is nothing to modify(). So, instead of modification, you need to compose XML.

SQL Server provides two XML composition methods natively:

The FOR XML clause instructs the Database Engine to return the result of a SELECT query as an XML document
XQuery, principally used to retrieve XML data, can also be used to create XML documents (or fragments).

You can try SQL Server native XML composition via XQuery. Check it out below two methods.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, Val INT, MyXML XML);
INSERT INTO @tbl (Val, MyXML) VALUES
(2, NULL),
(5, NULL);
-- DDL and sample data population, end

SELECT * FROM @tbl;

-- Method #1
UPDATE @tbl
SET MyXML = CAST(N'' AS XML).query('
<data>
   <val>{sql:column("Val")}</val>
</data>
');

SELECT * FROM @tbl;

-- Method #2
UPDATE @tbl
SET MyXML = CAST(N'' AS XML).query('
element data {
    element val { text {sql:column("Val")}
    }
}
');

SELECT * FROM @tbl;

Excellent - thanks for confirming that `.modify()` needs something to work with in the first place. Very clever use of `.query()` that I would never have thought of... and I've never seen the 2nd example format of data before. But for ease of use I'm going with the `FOR XML` route — freefaller, Oct 31 '22 at 16:58

TSQL using modify to initialise a null column

2 Answers2