Remove values from my XML in Sql Query Results

Question

I have an a log table that stores columns with XML data in it. I am trying to extract unique values from this table.

The XML for one of the rows looks similar to this:

<SomeRoot>
  <Parameters>
      <OrderContracts attr0="OrderContractsArray" isNull="False">
        <OrderedContract0>
          <RandomNumber>1234567</RandomNumber>
          <RandomDateTime>10/14/2015 00:01:00</RandomDateTime>
          <PeformOverride>False</PeformOverride>
          <KeeperID>1355654654</KeeperID>
          <OrderGuid>cbfd2f3f-0920-4df4-83b6-323a94a7e98d</OrderGuid>
          <PriorityId>159</PriorityId>
        </OrderedContract0>
          <OrderedContract1>
          <RandomNumber>95147</RandomNumber>
          <RandomDateTime>10/14/2015 00:01:00</RandomDateTime>
          <PeformOverride>False</PeformOverride>
          <KeeperID>4656176381</KeeperID>
          <OrderGuid>844fe0a8-2708-4acd-b990-598cba8b06f2</OrderGuid>
          <PriorityId>1596</PriorityId>
        </OrderedContract1>
        <OrderedContract2>
          <RandomNumber>3</RandomNumber>
          <RandomDateTime>10/14/2015 00:01:00</RandomDateTime>
          <PeformOverride>False</PeformOverride>
          <KeeperID>13064244653</KeeperID>
          <OrderGuid>55486e10-c7a9-49d8-b5d6-d310a1a598a6</OrderGuid>
          <PriorityId>156</PriorityId>
        </OrderedContract2>
      </OrderContracts>
      <SomeOtherValue attr0="SomeOtherValueArray" isNull="True" />  
  </Parameters>
</SomeRoot>

I am wondering how I could query this table to get back a list of results that have the random elements removed. Something like this:

<SomeRoot>
    <Parameters>
        <OrderContracts attr0="OrderContractsArray" isNull="False">
            <OrderedContract0>
                <PeformOverride>False</PeformOverride>
                <KeeperID>1355654654</KeeperID>             
                <PriorityId>159</PriorityId>
            </OrderedContract0>
            <OrderedContract1>              
                <PeformOverride>False</PeformOverride>
                <KeeperID>4656176381</KeeperID>             
                <PriorityId>1596</PriorityId>
            </OrderedContract1>
            <OrderedContract2>          
                <PeformOverride>False</PeformOverride>
                <KeeperID>13064244653</KeeperID>                
                <PriorityId>156</PriorityId>
            </OrderedContract2>
        </OrderContracts>
        <SomeOtherValue attr0="SomeOtherValueArray" isNull="True" />
    </Parameters>
</SomeRoot>

I have seen some XML methods to modify a single XML Document, but nothing that I can figure out how to use on a result set.

Is there a way to remove specific nodes from a result set (but not modify the underlying data)?

radar · Accepted Answer · 2015-12-01 03:50:36Z

1

one option is to store the data in a temp table and then use delete xml (DML)

declare @tempTable table
(
  data xml
)

insert into @tempTable
select data 
from Table1

update @tempTable
set data.modify(' delete //RandomNumber') 

update @tempTable
set data.modify(' delete //RandomDateTime') 



select * from @tempTable

answered Dec 1, 2015 at 3:50

radar

13.4k2 gold badges27 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

Remove values from my XML in Sql Query Results

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