在Sql Query Results中从我的XML中删除值

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

我有一个日志表,用于存储包含XML数据的列。我试图从这个表中提取唯一值。

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

其中一行的XML看起来类似于:

<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.

我已经看到一些XML方法来修改单个XML文档,但我无法弄清楚如何在结果集上使用它。

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

有没有办法从结果集中删除特定节点(但不能修改基础数据)?

1 个解决方案

#1

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

一个选项是将数据存储在临时表中,然后使用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