Delete a row in a XML column of a SQL Server table based on attribute value

Question

I have a XML column in a SQL Server table.

The data looks like this:

<ColumnLayout>
    <LayoutColumns>
        <Column PropertyId="43" ColumnWidth="0" IsHidden="false" IsDefaultColumn="false" />
        <Column PropertyId="12" ColumnWidth="0" IsHidden="false" IsDefaultColumn="false" />
        <Column PropertyId="41" ColumnWidth="0" IsHidden="false" IsDefaultColumn="false" />
        <Column PropertyId="16" ColumnWidth="0" IsHidden="false" IsDefaultColumn="false" />
        <Column PropertyId="23" ColumnWidth="0" IsHidden="false" IsDefaultColumn="false" />
        <Column PropertyId="94" ColumnWidth="0" IsHidden="false" IsDefaultColumn="false" />
        <Column PropertyId="98" ColumnWidth="0" IsHidden="false" IsDefaultColumn="false" />
    </LayoutColumns>
</ColumnLayout>

I want to delete a column node which contains PropertyId = 43.

How to query that and update the XML column in that table?

marc_s · Accepted Answer · 2017-10-24 11:47:47Z

3

Just use the XQuery .modify() method with a delete command:

UPDATE dbo.YourTable
SET Content.modify('delete /ColumnLayout/LayoutColumns/Column[@PropertyId=43]')

answered Oct 24, 2017 at 11:47

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete a row in a XML column of a SQL Server table based on attribute value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related