Search for an attribute with a value in XML column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Search for an attribute with a value in XML column

Author

Topic

jayram
Starting Member

47 Posts

Posted - 2014-06-10 : 11:43:40

I have a table with Content column which is XML data type

I use the below query to search for attribute PROV_TYPE with a value IPF

SELECT top 10 content.query('/CLAIM/INPUT/HEADER[@PROV_TYPE="APC"]')
FROM [OutputData]
WHERE Id IN (
'4b2bbf7b-cafd-43bf-8f55-eb7bcaf8073c'
,'7d78e9fc-f409-4138-b003-48976c110735'
)

the above query returns 10 rows back but all rows except 1 are blank which is weird. The one row that has a value has the xml in it which has <HEADER PROV_TYPE="IPF"............

The below query returns blank

SELECT TOP 10 *
FROM [OutputData]
WHERE content.value('(/CLAIM/INPUT/HEADER)[1]', 'nvarchar(max)') LIKE 'IPF%'
and Id IN (
'4b2bbf7b-cafd-43bf-8f55-eb7bcaf8073c'
,'7d78e9fc-f409-4138-b003-48976c110735'
)

what am i doing wrong. the below is the example of the claim XML

<CLAIM version="native">
<INPUT>
<HEADER PROV_TYPE="IPF" TOB="111"...............>
<CODES>
<CODE CODE="A1" AMT="1132.00" />
<CODE CODE="01" AMT="751.00" />
</CODES>
.
.
.
.
.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-10 : 13:56:00

quote:
Originally posted by jayram

I have a table with Content column which is XML data type

I use the below query to search for attribute PROV_TYPE with a value IPF

SELECT top 10 content.query('/CLAIM/INPUT/HEADER[@PROV_TYPE="APC"]')
FROM [OutputData]
WHERE Id IN (
'4b2bbf7b-cafd-43bf-8f55-eb7bcaf8073c'
,'7d78e9fc-f409-4138-b003-48976c110735'
)

the above query returns 10 rows back but all rows except 1 are blank which is weird. The one row that has a value has the xml in it which has <HEADER PROV_TYPE="IPF"............

The below query returns blank

SELECT TOP 10 *
FROM [OutputData]
WHERE content.value('(/CLAIM/INPUT/HEADER)[1]', 'nvarchar(max)') LIKE 'IPF%'
and Id IN (
'4b2bbf7b-cafd-43bf-8f55-eb7bcaf8073c'
,'7d78e9fc-f409-4138-b003-48976c110735'
)

what am i doing wrong. the below is the example of the claim XML

<CLAIM version="native">
<INPUT>
<HEADER PROV_TYPE="IPF" TOB="111"...............>
<CODES>
<CODE CODE="A1" AMT="1132.00" />
<CODE CODE="01" AMT="751.00" />
</CODES>
.
.
.
.
.

What is the output you are trying to get? If you are looking for all rows where that specific attribute with that specific value is present, use exist function in a where clause - like this:


SELECT * FROM [OutputData]
WHERE
   content.exist('/CLAIM/INPUT/HEADER[@PROV_TYPE="IPF"]') = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources