Delete attribute from XML
I am trying to remove a few attributes from XML, but the attribute name is dynamic and is a calculated field in the table
This is my test SQL, and it works when I hard-code the attribute name:
Code:
DECLARE @XML XML = '<DataGridView_Data>
<Row_Data Row_Index="1" Value_56="123" Value_57="dfgsd" Value_60="True" Value_71="78bfc25b-0af0-4bd2-841b-d66cbce13858|2|2023-03-22T14:25:52.8723830-04:00|1" Value_75="1|30|2023-03-22T14:25:53.5222980-04:00" />
<Row_Data Row_Index="2" Value_56="343" Value_57="sdfg" Value_60="False" Value_71="78bfc25b-0af0-4bd2-841b-d66cbce13858|2|2023-03-22T14:26:09.7227704-04:00|1" Value_75="1|30|2023-03-22T14:26:07.3063956-04:00" />
<Row_Data Row_Index="3" Value_56="jsygfjdalg" Value_57="" Value_60="False" Value_71="78bfc25b-0af0-4bd2-841b-d66cbce13858|2|2023-06-09T16:39:42.6530859-04:00|1" />
<Row_Data Row_Index="4" Value_56="adgdsfg" Value_57="" Value_60="False" />
<Row_Data Row_Index="5" Value_56="sdfg" Value_57="" Value_60="False" />
<Row_Data Row_Index="6" Value_56="dsfg" Value_57="" Value_60="False" Value_71="78bfc25b-0af0-4bd2-841b-d66cbce13858|2|2023-06-09T16:39:44.9092913-04:00|1" />
<Row_Data Row_Index="7" Value_56="dsfg" Value_57="" Value_60="False" />
<Row_Data Row_Index="8" Value_56="sdfgds" Value_57="" Value_60="False" Value_71="78bfc25b-0af0-4bd2-841b-d66cbce13858|2|2023-06-09T16:39:46.4054980-04:00|1" />
<Row_Data Row_Index="9" Value_56="fg" Value_57="" Value_60="False" Value_71="78bfc25b-0af0-4bd2-841b-d66cbce13858|2|2023-06-09T16:39:49.4204060-04:00|1" />
<Row_Data Row_Index="10" Value_56="dsfg" Value_57="" Value_60="False" Value_71="78bfc25b-0af0-4bd2-841b-d66cbce13858|2|2023-06-09T16:39:51.7429717-04:00|1" />
</DataGridView_Data>'
DECLARE @tbl TABLE (col XML, ID INT, remove_item_name VARCHAR(100))
INSERT INTO @tbl VALUES (@XML, 5, 'Value_71')
INSERT INTO @tbl VALUES (@XML, 5, 'Value_75')
SELECT * FROM @tbl
UPDATE tbl
SET col.modify('delete /DataGridView_Data/Row_Data/@Value_71')
FROM @tbl AS tbl
WHERE ID = 5
SELECT * FROM @tbl
But what I actually need is something like this:
Code:
SET col.modify('delete /DataGridView_Data/Row_Data/[sql:column("remove_item_name")]')
Which does not work, and I can't figure out how to make it work
Re: Delete attribute from XML
Guys! I asked ChatGPT, and it gave me the answer!
Check this out!
Code:
UPDATE tbl
SET col.modify('delete /DataGridView_Data/Row_Data/@*[local-name()=sql:column("remove_item_name")]')
FROM @tbl AS tbl
WHERE ID = 5
