1

I am trying to select the rows from the table by putting the condition on the xml column. The condition like if there is date value on the node ‘Completedon’ in the xml column then select that row. For this above requirement I wrote one query, that is

SELECT [Tasks].* FROM [TaskManager].dbo.[Tasks] WHERE ( [Work details].exist('for $x in /Root/Row where  ( (empty(xs:date($x/Completedon[1])) or ($x/Completedon[1])="")  )  return $x')>0 )

But in this above query, it selects all the rows which has the ‘Completedon’ node as well as the row without this node. Please help me, where did I go wrong? And please help me to correct the query.. Thank you in advance…

  • Please provide some sample XML. – wBob Mar 30 '16 at 10:32
1

I have inferred some XML structure from your query but it is best if you supply some sample XML. Does this work for you?

SELECT *
FROM dbo.Tasks 
WHERE [Work details].exist('Root/Row/Completedon/text()') = 1

My full repro script:

CREATE TABLE dbo.Tasks
(
    taskId          INT IDENTITY PRIMARY KEY,
    [Work details]  XML
)
GO


INSERT INTO dbo.Tasks ( [Work details] )
VALUES ( '<Root>
    <Row>
        <Completedon>01/04/2016</Completedon>
    </Row>
</Root>' ),
( '<Root>
    <Row>
        <Completedon></Completedon>
    </Row>
</Root>' )
,
( '<Root>
    <Row>
        <Completedon/>
    </Row>
</Root>' )


GO


-- Original query; returns all records
SELECT [Tasks].* 
FROM dbo.[Tasks] 
WHERE ( [Work details].exist('for $x in /Root/Row where  ( (empty(xs:date($x/Completedon[1])) or ($x/Completedon[1])="")  )  return $x')>0 )


-- Revised query
SELECT *
FROM dbo.Tasks 
WHERE [Work details].exist('Root/Row/Completedon/text()') = 1

And test results:

Test results

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.