CODEWITHSUNDEEP
sql-serverxml
Ehsan Akbar
Ehsan Akbar

Reputation: 7281

SQL Server xml query can't return expected result

I have this xml data in my table as you can see :

<ArrayOfFlowDetailParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <FlowDetailParameters>
    <DepartmentId>23</DepartmentId>
    <UserId xsi:nil="true" />
    <Username />
    <FullName />
    <ConfirmDateTime xsi:nil="true" />
    <Comment />
    <Status>Pending</Status>
    <AttachmentId />
  </FlowDetailParameters>
  <FlowDetailParameters>
    <DepartmentId>22</DepartmentId>
    <UserId xsi:nil="true" />
    <Username />
    <FullName />
    <ConfirmDateTime xsi:nil="true" />
    <Comment />
    <Status>Pending</Status>
    <AttachmentId />
  </FlowDetailParameters>
  <FlowDetailParameters>
    <DepartmentId>7</DepartmentId>
    <UserId xsi:nil="true" />
    <Username />
    <FullName />
    <ConfirmDateTime xsi:nil="true" />
    <Comment />
    <Status>Pending</Status>
    <AttachmentId />
  </FlowDetailParameters>
  <FlowDetailParameters>
    <DepartmentId>18</DepartmentId>
    <UserId xsi:nil="true" />
    <Username />
    <FullName />
    <ConfirmDateTime xsi:nil="true" />
    <Comment />
    <Status>Pending</Status>
    <AttachmentId />
  </FlowDetailParameters>
</ArrayOfFlowDetailParameters>

when i want to find the departmentid with value=22 my query returns 0 result but when i search value=23 it returns 1 result i think it is because of [1]. 

 declare @departmentId nvarchar(max)
 set @departmentId=22
 select  Requests.*   from Requests
 where    
 and (FlowDetailParameter.value('(/ArrayOfFlowDetailParameters/FlowDetailParameters/DepartmentId/text())[1]','bigint') = @departmentId  )

Upvotes: 0

Views: 46

Answers (1)

Max Szczurek
Max Szczurek

Reputation: 4334

It sounds like you're trying to query a list of requests where the FlowDetailParameter column's XML contains a record where the DepartmentId matches your @departmentId variable, right?

The [1] in your query specifies to only check the first occurrence of DepartmentId in each row's FlowDetailParameter XML. You won't get a match unless the first DepartmentId in the XML matches your parameter.

Instead, you can use the following query to find all requests that have a FlowDetailParameter matching the @departmentid variable using the nodes() method.

SELECT r.*
FROM @Requests r
WHERE EXISTS (SELECT *
        FROM r.FlowDetailParameter.nodes('/ArrayOfFlowDetailParameters/FlowDetailParameters/DepartmentId') as Parms(DepartmentId)
        WHERE DepartmentId.value('.', 'bigint') = @departmentid)

Upvotes: 2

Related Questions