XQuery help

[AHarvey]

Hi,

I need to run a report in SQL that checks the results of a table where each row has an xml feed. The item I need to search by is stored withing the xml feed.

I've pulled the required rows into a temp table, nearly 5000 of them. Then I've got this code so far:

SELECT * FROM #TempTbl
WHERE RESULT_XML.exist('declare namespace ns= "http://URL/dataservices/schemas/core/v1.0/WorkflowMessage.xsd";
(/ns:WorkflowMessage/ns:ServiceMessageBlocks/ns:ServiceMessageBlock/ns:Response[contains(.,"pass")])')=1

This returns 2.5k rows, the problem is that it could be picking up 'PASS' from the wrong areas as well so I need to drill down more but I'm stuck with the XML structure.

I need to add in 7 further entries to the query but it fail's on the next one:

<res: XXXXX> I believe it fails due to the res:

then later I have a GUID unique to each row, so I need to add that row to the search but ignore the GUID, would a wildcard work for these?

Any suggestions?

 

[peterwalkley]

Can you put an example of the full XML up, either here or somewhere like pastebin ? XPath should be able to do what you want, but its a pig until you get used to it.

 

[AHarvey]

I could but I'd have to clean it first, it's work-related and contains personal information. I'll see if I can do it tomorrow as i've not brought my work laptop home tonight.