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

CodePudding user response：

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;

CodePudding user response：

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