Reputation: 371
i am looking to search an XML column called ClaimData in a SQL BatchProcessedClaims table.
CREATE TABLE [dbo].[BatchProcessedClaims](
[Id] [int] NOT NULL,
[Im] [varchar](50) NULL,
[ClaimData] [xml] NULL
) ON [PRIMARY]
Below is the value contained in one cell
<BGMSG>
<HEADER COMPLETION="Batch_22e3bc5f-d130-4a28-b078-4f3416360a35">
<ROUTING_SLIP>
<RECIPIENT Channel=".\private$\q_amb_leg" Delivered="1" DeliveryTime="2014-06-21T16:40:07.298" />
<RECIPIENT Channel=".\private$\22e3bc5f-d130-4a28-b078-4f3416360a35" Delivered="0" />
</ROUTING_SLIP>
</HEADER>
</BGMSG>
i am searching for all records with "Batch_22e3bc5f-d130-4a28-b078-4f3416360a35". i am trying it in 3 different ways method 1
SELECT TOP 10 *
FROM [BATCHES].[dbo].[BatchProcessedClaims]
WHERE ClaimData.value('(/BGMSG/HEADER)[1]', 'nvarchar(max)') LIKE 'Batch_22e3bc5f-d130-4a28-b078-4f3416360a35'
method 2
SELECT TOP 10 ClaimData.query('/BGMSG/HEADER[@COMPLETION="Batch_22e3bc5f-d130-4a28-b078-4f3416360a35"]')
FROM [BATCHES].[dbo].[BatchProcessedClaims]
method 3
SELECT top 10 *
FROM (
SELECT pref.value('(text())[1]', 'varchar(75)') AS Batchname
FROM BatchProcessedClaims
CROSS APPLY ClaimData.nodes('/BGMSG/HEADER') AS ClaimData(pref)
) AS Result
WHERE Batchname LIKE 'Batch_22e3bc5f-d130-4a28-b078-4f3416360a35'
Method 1 runs for 6 minutes and Method 3 runs for like 13 minutes and gives no results and method 2 gives me 10 blank records as results. What am i doing wrong
i tried method 4
SELECT top 10 *
FROM BatchProcessedClaims
WHERE CONTAINS(ClaimData,'Batch_22e3bc5f-d130-4a28-b078-4f3416360a35')
AND xCol.exist('/BGMSG/HEADER/text()[contains(.,"Batch_22e3bc5f-d130-4a28-b078-4f3416360a35")]') =1
but i currently do not have full-text indexed and i get an error "Msg 7601, Level 16, State 2, Line 1 Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'BatchProcessedClaims' because it is not full-text indexed."
i am trying to accomplish with out enabling full-text indexed by method 1 to 3
Any help
Thanks
Upvotes: 1
Views: 596
Reputation: 16588
Either of these should work fairly efficiently:
select top 10 * from BatchProcessedClaims
where claimdata.value('(/BGMSG/HEADER/@COMPLETION)[1]', 'nvarchar(max)') = 'Batch_22e3bc5f-d130-4a28-b078-4f3416360a35'
OR
select top 10 * from BatchProcessedClaims
where claimdata.exist('/BGMSG/HEADER[@COMPLETION[1] = "Batch_22e3bc5f-d130-4a28-b078-4f3416360a35"]') = 1
The exist
version should be faster, but you'll want to test against your dataset.
Upvotes: 2