2

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
  • 732,580
  • 175
  • 1,330
  • 1,459
Sparrow
  • 355
  • 4
  • 19

1 Answers1

3

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

UPDATE dbo.YourTable
SET Content.modify('delete /ColumnLayout/LayoutColumns/Column[@PropertyId=43]')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459