ID           NVARCAHR(64)
NAME         NVARCAHR(128)
ExtraInfo    NVARCAHR(MAX)

<ModifyMetadata>
    <hostName t="ws">host2</hostName>
    <Name t="ws">myname</Name>
    <modifiedFields t="lt">
        <ExtraInfoAdditionalField>
            <FieldName t="ws">foo</FieldName>
            <Values t="lws">
                <s/>
            </Values>
            <FieldType t="ws">String</FieldType>
        </ExtraInfoAdditionalField>
        <ExtraInfoAdditionalField>
            <FieldName t="ws">bar</FieldName>
            <Values t="lws">
                <s/>
            </Values>
            <FieldType t="ws">String</FieldType>
        </ExtraInfoAdditionalField>
        </modifiedFields>
</ModifyMetadata>

SELECT *
FROM [JOB]
WHERE ExtraInfo LIKE '%></s>%' ESCAPE ''

SELECT CAST(ExtraInfo AS XML)
FROM [JOB]
WHERE ExtraInfo.value('XQUERYHERE') IS NULL

DECLARE @ExtraInfo XML
SET @ExtraInfo = CAST([MYDB].[dbo].[JOB].ExtraInfo AS XML)

SELECT ExtraInfo
FROM [JOB]
WHERE @ExtraInfo.value('XQUERYHERE') IS NULL

SELECT CAST(ExtraInfo AS XML) as myinfo
FROM [JOB]
WHERE myinfo.value('XQUERYHERE') IS NULL

SELECT Id, [Name], ExtraInfo
FROM [JOB]
WHERE CAST(ExtraInfo as xml).value('XQUERYHERE') IS NULL