CODEWITHSUNDEEP
sqlsql-serverxml
user176047
user176047

Reputation: 371

Search an XML column for a value/string

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

Answers (1)

mwigdahl
mwigdahl

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

Related Questions