Query Xml data in Sql Server holding serialized objects

Question

We are storing some serialized objects in an Xml-column in Sql Server.

I would like to create a query that gives me the list of serialized objects (rows) where a specific field has a specific value.

My Xml looks like this:

<MessageContainer xmlns="http://schemas.datacontract.org/2004/07/D3A.Messages" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="1" z:Type="D3A.Messages.MessageContainer" z:Assembly="D3A.Messages, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
  <Messages xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" z:Id="2" z:Type="System.Collections.Generic.List`1[[D3A.Messages.IMessage, D3A.Messages, Version=1.0.0.0, Culture=neutral,   PublicKeyToken=null]]" z:Assembly="0">
    <a:_items z:Id="3" z:Size="512">
      <a:anyType z:Id="4" z:Type="D3A.Messages.MessageAlarmLog" z:Assembly="D3A.Messages, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
        <_x003C_Id_x003E_k__BackingField>00000000-0000-0000-0000-000000000000</_x003C_Id_x003E_k__BackingField>
        <_x003C_StorageOperation_x003E_k__BackingField>Create</_x003C_StorageOperation_x003E_k__BackingField>
        <_x003C_Comment_x003E_k__BackingField z:Id="5" />
        <_x003C_ErrorCodeTranslation_x003E_k__BackingField>Ok</_x003C_ErrorCodeTranslation_x003E_k__BackingField>
        <_x003C_ErrorCode_x003E_k__BackingField>0</_x003C_ErrorCode_x003E_k__BackingField>
        <_x003C_GroupType_x003E_k__BackingField>System</_x003C_GroupType_x003E_k__BackingField>
        <_x003C_LogType_x003E_k__BackingField z:Id="6">1</_x003C_LogType_x003E_k__BackingField>
        <_x003C_Parameter_x003E_k__BackingField z:Id="7">Timed out.</_x003C_Parameter_x003E_k__BackingField>
        <_x003C_TimeStampOff_x003E_k__BackingField i:nil="true" />
        <_x003C_TimeStamp_x003E_k__BackingField>2014-05-10T03:10:04Z</_x003C_TimeStamp_x003E_k__BackingField>
        <_x003C_Unit_x003E_k__BackingField z:Id="8">100001</_x003C_Unit_x003E_k__BackingField>
        <_x003C_UserName_x003E_k__BackingField z:Id="9">e2_fælles</_x003C_UserName_x003E_k__BackingField>
        <_x003C_Value_x003E_k__BackingField z:Id="10">50101</_x003C_Value_x003E_k__BackingField>
        <_x003C_WindFarm_x003E_k__BackingField z:Id="11">HR2</_x003C_WindFarm_x003E_k__BackingField>
      </a:anyType>
      <a:anyType z:Id="12" z:Type="D3A.Messages.MessageAlarmLog" z:Assembly="D3A.Messages, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
        <_x003C_Id_x003E_k__BackingField>00000000-0000-0000-0000-000000000000</_x003C_Id_x003E_k__BackingField>
        <_x003C_StorageOperation_x003E_k__BackingField>Create</_x003C_StorageOperation_x003E_k__BackingField>
        <_x003C_Comment_x003E_k__BackingField z:Ref="5" i:nil="true" />
        <_x003C_ErrorCodeTranslation_x003E_k__BackingField>Ok</_x003C_ErrorCodeTranslation_x003E_k__BackingField>
        <_x003C_ErrorCode_x003E_k__BackingField>0</_x003C_ErrorCode_x003E_k__BackingField>
        <_x003C_GroupType_x003E_k__BackingField>System</_x003C_GroupType_x003E_k__BackingField>
        <_x003C_LogType_x003E_k__BackingField z:Id="13">1</_x003C_LogType_x003E_k__BackingField>
        <_x003C_Parameter_x003E_k__BackingField z:Ref="5" i:nil="true" />
        <_x003C_TimeStampOff_x003E_k__BackingField i:nil="true" />
        <_x003C_TimeStamp_x003E_k__BackingField>2014-05-10T03:10:09Z</_x003C_TimeStamp_x003E_k__BackingField>
        <_x003C_Unit_x003E_k__BackingField z:Id="14">100001</_x003C_Unit_x003E_k__BackingField>
        <_x003C_UserName_x003E_k__BackingField z:Id="15">e2_fælles</_x003C_UserName_x003E_k__BackingField>
        <_x003C_Value_x003E_k__BackingField z:Id="16">50100</_x003C_Value_x003E_k__BackingField>
        <_x003C_WindFarm_x003E_k__BackingField z:Ref="11" i:nil="true" />
      </a:anyType>
    </a:_items> 
  </Messages>
</MessageContainer>

I am particularly interested in those Xml-chunks that have a specific value in the Xml-field called _x003C_Unit_x003E_k__BackingField. There may be many <a:anytype>-fragments, and I am interested in a result with the complete <MessagesContainer>-fragments with at least one <a:anyType> matching my criteria.

Can you help here? I cannot seem to get the namespace declarations in place in the query.

Thank you :-)

Answer 1

In the query below, replace "100001" with the required value. You can use sql:variable() to make this value dynamic if required.

select @x.query('
    declare namespace d3a="http://schemas.datacontract.org/2004/07/D3A.Messages";
    declare namespace a="http://schemas.microsoft.com/2003/10/Serialization/Arrays";
    /d3a:MessageContainer[
        d3a:Messages[
            a:_items[
                a:anyType[d3a:_x003C_Unit_x003E_k__BackingField/text() = "100001"]
            ]
        ]
    ]'
);

See this article for more details on how to handle namespaces in XQuery.