Searching for "|" pipe symbol in XML column in SQL server

Question

I am using a script like this to look for a "|" symbol in a XML column no matter where it appears. I know there are pipes in there but the below query gives me empty results

SELECT TOP 100 *
FROM 
    [DB].[dbo].[InputData]
WHERE 
    Content.exist('//.[text() = "|"]') = 1
    AND DataFileId = '75d48aed6327'

What am I doing wrong? This is the xml content of the column Content:

<CLAIM version="native">
   <INPUT>
      <HEADER BIRTH_DT="1/1/1941">
         <DIAG_CODES>
            <DX CODE="7234" />
         </DIAG_CODES>
         <CON_CODES>
            <CON_CODE VALUE="M0" />
         </CON_CODES>
         <VAL_CODES>
            <VAL_CODE CODE="A2" AMT="604.03" />
         </VAL_CODES>
      </HEADER>
</CLAIM>

You're missing a `` element in your XML (or is that just a typo in your question?) And also: your sample XML doesn't show any `|` character, either..... — marc_s, Jun 02 '14 at 19:00
i am missing . That is just a sample. The XML is very long but there could be a pipe symbol in this tag "123|345" and few other tags — user176047, Jun 02 '14 at 19:11
@user176047 - Your SQL query is looking for nodes that *equal* `|`, not nodes that *contain* `|`. Big difference. — Mike Christensen, Jun 02 '14 at 21:03

score 2 · Answer 1 · edited May 23 '17 at 12:11

Hi was looking the answer and found it here https://stackoverflow.com/a/11738172/1692632

DECLARE @xmlTable TABLE (xmlData XML)

INSERT INTO @xmlTable SELECT N'
<CLAIM version="native">
      <HEADER BIRTH_DT="1/1/1941">
         <DIAG_CODES>
            <DX CODE="7234" />
         </DIAG_CODES>
         <CON_CODES>
            <CON_CODE VALUE="M0" />
         </CON_CODES>
         <VAL_CODES>
            <VAL_CODE CODE="A2" AMT="604.03" />
         </VAL_CODES>
      </HEADER>
</CLAIM>
'
INSERT INTO @xmlTable SELECT N'
<CLAIM version="native">
      <HEADER BIRTH_DT="1/1/1941">
         <DIAG_CODES>
            <DX CODE="72|34" />
         </DIAG_CODES>
         <CON_CODES>
            <CON_CODE VALUE="M0" />
         </CON_CODES>
         <VAL_CODES>
            <VAL_CODE CODE="A2" AMT="604.03" />
         </VAL_CODES>
      </HEADER>
</CLAIM>
'

SELECT T.*
FROM @xmlTable AS T
CROSS APPLY T.xmlData.nodes('//@*') as tx(r)
WHERE tx.r.value('contains((.),"|")','bit')=1

Also you can try this one as you tried (this gives without duplicates):

SELECT TOP 100 *
FROM 
    @xmlTable
WHERE 
    xmlData.exist('//@*[contains(., "|")]') = 1

score -2 · Answer 2 · answered Jun 02 '14 at 18:57

-2

SELECT TOP 100 *
FROM [DB].[dbo].[InputData]
WHERE DataFileId = '75d48aed6327'
and charindex('|',Content) > 1

answered Jun 02 '14 at 18:57

Jester

3,069
5
30
44

1

`-1` - This would match on a `|` *anywhere* in the text, not just an XML text value. – Mike Christensen Jun 02 '14 at 18:59

Searching for "|" pipe symbol in XML column in SQL server

2 Answers2