XQuery a SQL Server NVARCHAR column

Question

I have a table with a nvarchar column which stores XML. I'm trying to find all rows which has a specific node empty

Here is the structure of the table JOB:

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

Here is a sample of the content of the column ExtraInfo, I'm looking for:

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

With this sample basically I'm looking for all rows with the node ModifyMetadata and where the nodes ExtraInfoAdditionalField\Values are empty (this is my understanding of what <s/> means).

First I tried with a simple LIKE in with this query:

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

But this query also returns rows where the nodes ExtraInfoAdditionalField\Values have some content.

The XQuery way: I switched to another method where I'm trying to CAST the column to XML and then return only the row where the nodes ExtraInfoAdditionalField\Values are empty.

But then I'm getting confused about the methodolgy to query this column with XQuery or any other available methodology.

I have tried to CAST directly in the SELECT

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

But SQL Server doesn't know what ExtraInfo. value is.

I tried to CAST it in a variable:

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

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

But SQL doesn't understand what is [MYDB].[dbo].[JOB].ExtraInfo

I have tried to CAST directly in the SELECT and rename it:

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

But T-SQL doesn't know what myinfo is.

So I'm clearly lacking of understanding in what way It possible to XML query a column which is stored in NVARCHAR format

I hope this I this is clear enough, thanks in advance for any input

Answer 1

What you didn't try is the obvious solution - which is to cast the ExtraInfo column to xml inside the where clause:

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

Answer 2

This answer comes rather late, so I don't know if you still need this...

First of all: It is a bad idea to store XML as VARCHAR(MAX). If you can change this, you should use the native XML data-type.

However, it is important to know, that XML does not know a NULL (uhm, there is xsi:nil, but that's something else), but it knows missing elements (which are translated to NULL in T-SQL) and empty elements, which you can query using empty(SomeXPathHere) or - vice versa - not(empty(SomeXPathHere)).

You did not state your expected output, so I'll provide several approaches and hope the one you need is included:

DECLARE @YourTable TABLE(ID NVARCHAR(64), [NAME] NVARCHAR(128), ExtraInfo NVARCHAR(MAX));
INSERT INTO @YourTable VALUES
 ('id1','both without',
 N'<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>')
,('id2','one with and one without content',
 N'<ModifyMetadata>
    <hostName t="ws">host2</hostName>
    <Name t="ws">myname</Name>
    <modifiedFields t="lt">
        <ExtraInfoAdditionalField>
            <FieldName t="ws">foo</FieldName>
            <Values t="lws">
                <s>SomeValueHere</s>
            </Values>
            <FieldType t="ws">String</FieldType>
        </ExtraInfoAdditionalField>
        <ExtraInfoAdditionalField>
            <FieldName t="ws">bar</FieldName>
            <Values t="lws">
                <s/> <!-- No value here -->
            </Values>
            <FieldType t="ws">String</FieldType>
        </ExtraInfoAdditionalField>
        </modifiedFields>
</ModifyMetadata>')
,('id2','both with content',
 N'<ModifyMetadata>
    <hostName t="ws">host2</hostName>
    <Name t="ws">myname</Name>
    <modifiedFields t="lt">
        <ExtraInfoAdditionalField>
            <FieldName t="ws">foo</FieldName>
            <Values t="lws">
                <s>SomeValueHere</s>
            </Values>
            <FieldType t="ws">String</FieldType>
        </ExtraInfoAdditionalField>
        <ExtraInfoAdditionalField>
            <FieldName t="ws">bar</FieldName>
            <Values t="lws">
                <s>And here too</s>
            </Values>
            <FieldType t="ws">String</FieldType>
        </ExtraInfoAdditionalField>
        </modifiedFields>
</ModifyMetadata>');

--this returns all rows where at least on <s> is empty

SELECT t.ID
      ,t.[NAME]
      ,Casted
FROM @YourTable t
CROSS APPLY (SELECT CAST(t.ExtraInfo AS XML)) A(Casted)
WHERE Casted.exist('/ModifyMetadata/modifiedFields/ExtraInfoAdditionalField[empty(Values/s/text())]')=1

--this returns a list of all fields (each field as separate row) if <s> is empty

SELECT t.ID
      ,t.[NAME]
      ,B.dt.value('(text())[1]','nvarchar(max)') FieldWithNonEmpty_s
      ,B.dt.query('.') TheNode
FROM @YourTable t
CROSS APPLY (SELECT CAST(t.ExtraInfo AS XML)) A(Casted)
CROSS APPLY Casted.nodes('/ModifyMetadata/modifiedFields/ExtraInfoAdditionalField[empty(Values/s/text())]/FieldName') B(dt)

--this returns a list of all fields (each field as separate row) and will carry a NULL if <s> is empty

SELECT t.ID
      ,t.[NAME]
      ,B.dt.value('(FieldName/text())[1]','nvarchar(max)') FieldWithNonEmpty_s
      ,B.dt.value('(Values/s/text())[1]','nvarchar(max)') TheTextInS
      ,B.dt.query('.') TheNode
FROM @YourTable t
CROSS APPLY (SELECT CAST(t.ExtraInfo AS XML)) A(Casted)
OUTER APPLY Casted.nodes('/ModifyMetadata/modifiedFields/ExtraInfoAdditionalField[Values/s/text()]') B(dt);