Search code examples
sql-serverxmlt-sqlsql-server-2016xml.modify

TSQL using modify to initialise a null column

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>')
Solution

  • 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