Editing xml using SQL

Question

I'm new the xml data type in SQL Server. I have a field with some xml that I want to edit. In essence I want to delete certain nodes if they have a certain datatype or value. There are many subtype nodes and each subtype can have many SubtypeFieldInfo.

In cases where a SubtypeFieldInfo node contains a node :

<DefaultValue xsi:type="xs:dateTime">2222-02-22T00:00:00</DefaultValue>

I would like to delete that node

Here is the full xml. Any help much appreciated

    <DEFeatureClassInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:typens="http://www.esri.com/schemas/ArcGIS/10.0" xsi:type="typens:DEFeatureClassInfo">
  <Subtypes xsi:type="typens:ArrayOfSubtype">
    <Subtype xsi:type="typens:Subtype">
      <SubtypeName>1</SubtypeName>
      <SubtypeCode>1</SubtypeCode>
      <FieldInfos xsi:type="typens:ArrayOfSubtypeFieldInfo">
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>Status</FieldName>
          <DomainName>pwStatus</DomainName>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>OwnedBy</FieldName>
          <DomainName>shdMaintainedBy</DomainName>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>MaintainedBy</FieldName>
          <DomainName>shdMaintainedBy</DomainName>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>InputCode</FieldName>
          <DomainName />
          <DefaultValue xsi:type="xs:int">12</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>UnitType</FieldName>
          <DomainName>trafStripingType</DomainName>
          <DefaultValue xsi:type="xs:string">SY</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>CalTDescription</FieldName>
          <DomainName>trafStripingDescr</DomainName>
          <DefaultValue xsi:type="xs:string">CL</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>DetailNumber</FieldName>
          <DomainName />
          <DefaultValue xsi:type="xs:string">1</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>StripingDetail</FieldName>
          <DomainName>trafStripingDetail</DomainName>
          <DefaultValue xsi:type="xs:string">2WYCENTERLINE</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>Color</FieldName>
          <DomainName>trafStripingColor</DomainName>
          <DefaultValue xsi:type="xs:string">Yellow</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>LinePattern</FieldName>
          <DomainName>trafStripeLineType</DomainName>
          <DefaultValue xsi:type="xs:string">Skip</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>Material</FieldName>
          <DomainName>trafStripingMaterial</DomainName>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>TravelDirection</FieldName>
          <DomainName>trafTravelDirection</DomainName>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>IsSchoolZone</FieldName>
          <DomainName>shdBooleanYesNo</DomainName>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>EstInstallDate</FieldName>
          <DomainName />
          <DefaultValue xsi:type="xs:dateTime">2222-02-22T00:00:00</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>RefreshDate</FieldName>
          <DomainName />
          <DefaultValue xsi:type="xs:dateTime">2222-02-22T00:00:00</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>WOUpdateDate</FieldName>
          <DomainName />
          <DefaultValue xsi:type="xs:dateTime">2222-02-22T00:00:00</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>Quadrant</FieldName>
          <DomainName>shdQuadrantZone</DomainName>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>DwgAsbuiltDate</FieldName>
          <DomainName />
          <DefaultValue xsi:type="xs:dateTime">2222-02-22T00:00:00</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>DwgSignDate</FieldName>
          <DomainName />
          <DefaultValue xsi:type="xs:dateTime">2222-02-22T00:00:00</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>AcceptanceDate</FieldName>
          <DomainName />
          <DefaultValue xsi:type="xs:dateTime">2222-02-22T00:00:00</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>IsSyncHansen</FieldName>
          <DomainName>shdBooleanYesNo</DomainName>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>ImageDate</FieldName>
          <DomainName />
          <DefaultValue xsi:type="xs:dateTime">2222-02-22T00:00:00</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>ModifiedDate</FieldName>
          <DomainName />
          <DefaultValue xsi:type="xs:dateTime">2222-02-22T00:00:00</DefaultValue>
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>CreateDate</FieldName>
          <DomainName />
          <DefaultValue xsi:nil="true" />
        </SubtypeFieldInfo>
        <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
          <FieldName>DwgSheet</FieldName>
          <DomainName />
          <DefaultValue xsi:nil="true" />
        </SubtypeFieldInfo>
      </FieldInfos>
    </Subtype>

Mikael Eriksson · Accepted Answer · 2011-09-29 04:16:34Z

2

Not sure what node you want to delete so here are two options.

Delete the DefaultValue node:

update YourTable
set XMLCol.modify('delete (//SubtypeFieldInfo/DefaultValue)
                   [@xsi:type="xs:dateTime" and 
                    text() = "2222-02-22T00:00:00"]')

Delete the SubtypeFieldInfo node:

update YourTable
set XMLCol.modify('delete (//SubtypeFieldInfo)
                   [DefaultValue/@xsi:type="xs:dateTime" and 
                    DefaultValue/text() = "2222-02-22T00:00:00"]')

answered Sep 29, 2011 at 4:16

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

3 Comments

Dowlers Over a year ago

Thank you this does what I want.

Dowlers Over a year ago

Do you know of any resources online that go into this in more detail? I can't seem to find much when I Google it. Maybe I'm just using the wrong search terms.

Mikael Eriksson Over a year ago

@Dowlers - Here are some useful links. msdn.microsoft.com/en-us/library/ms187339.aspx msdn.microsoft.com/en-us/library/ms189075.aspx msdn.microsoft.com/en-us/library/… technet.microsoft.com/en-us/library/ms187107%28SQL.90%29.aspx

Collectives™ on Stack Overflow

Editing xml using SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related