How to check Xml Element's Attribute Value in SQL

Question

I want to select elements other than the elements whose InEx value is 'Excluded'.

Here is my XML document:

<MachineStatus>
<ShiftStop  MachineName="01" InEx="Excluded" />
<ShiftStop  MachineName="01" InEx="Included"  />
<ShiftStop  MachineName="01" InEx="Included"  />
  </MachineStatus>

I need to get

<ShiftStop  MachineName="01" InEx="Included"  />
<ShiftStop  MachineName="01" InEx="Included"  />

I tried this query but it does not work.

SELECT [Entity].query('/MachineStatus/ShiftStop') FROM [FES].[SMD].[Machine] WHERE 
[Entity].value('(/MachineStatus/ShiftStop/@InEx)[1]','varchar(10)') != 'Excluded'

Answer 1

When pulling values from XML, it does "rip" the entire tag, it extracts the elements. If you want to extract the values, and then get XML data back, you have to then rebuild the XML:

CREATE TABLE dbo.YourTable (ID int IDENTITY,
                            YourXML xml);
INSERT INTO dbo.YourTable (YourXML)
VALUES('<MachineStatus>
<ShiftStop  MachineName="01" InEx="Excluded" />
<ShiftStop  MachineName="01" InEx="Included"  />
<ShiftStop  MachineName="01" InEx="Included"  />
</MachineStatus>');

SELECT YT.ID,
       (SELECT MS.SS.value('@MachineName','char(2)') AS [ShiftStop/@MachineName],
               MS.SS.value('@InEx','varchar(8)') AS [ShiftStop/@InEx]
        FROM YT.YourXML.nodes('MachineStatus/ShiftStop')MS(SS)
        WHERE MS.SS.value('@InEx','varchar(8)') != 'Excluded'
        FOR XML PATH(''),TYPE)
FROM dbo.YourTable YT;

GO

DROP TABLE.dbo.YourTable;