XML Query within Multiple Rows of Data

Question

In my database table I have a XML column.

The value in the XML column is something like this:

Row1:

  <Data>
  <COLRowData>
  <DORowData>
  <PrimaryValue>Fever</PrimaryValue>  
  <EMRValue1> 101 <EMRValue1 />   
  <DataRowType>Past</DataRowType>
  <HasPositiveValue>True</HasPositiveValue>
  </DORowData>
  <DORowData>
  <PrimaryValue>Hypertension</PrimaryValue>
  <DataRowType>Present</DataRowType>
  <EMRValue1> No </EMRValue1>  
  <HasPositiveValue>False</HasPositiveValue>
 </DORowData>
 </COLRowData>
 </Data>

Row2:

 <Data>
 <COLRowData>
 <DORowData>
  <PrimaryValue>Diabetes</PrimaryValue>
  <DataRowType>Past</DataRowType>    
  <EMRValue1> No </EMRValue1>  
  <HasPositiveValue>True</HasPositiveValue>   
 </DORowData>
 <DORowData>
<PrimaryValue>Fever</PrimaryValue>
  <DataRowType>Present</DataRowType>
  <EMRValue1> 103  <EMRValue1 />
  <HasPositiveValue>True</HasPositiveValue>    
 </DORowData>    
 </COLRowData>
 </Data>

From this table I want to find all such rows where this following combination of Data exists in the single XML column:

PrimaryValue: Fever
DataRowType: Present

I am using this query:

Select * From TABLE 
WHERE  
   XML.exist('/Data/COLRowData/DORowData/DataRowType/text()[. = "Present"]') = 1 
   and
     XML.exist('/Data/COLRowData/DORowData/PrimaryValue/text()[. = "Fever"]') = 1

This query returns both the rows where as my requirement is that I should only find the Row2 as per the combination mentioned above.

Please help me with the query

Also how can i include the EMRValue1 - Value in Select statement from the XML satisfying the where clause?

Thanks i-one. But since my where clause is satisfying data from this block only

   <DORowData>
    <PrimaryValue>Fever</PrimaryValue>
    <DataRowType>Present</DataRowType>
    <EMRValue1> 103  <EMRValue1 />
     <HasPositiveValue>True</HasPositiveValue>    
    </DORowData>

The EMRValue1 in the Select clause should only return 1 row with the EMRValue1 = 103

i-one · Accepted Answer · 2013-07-15 14:50:27Z

1

You should combine both conditions inside the same XPath:

select * from [TABLE]
where
    XML.exist('/Data/COLRowData/DORowData[DataRowType/text()[. = "Present"] and PrimaryValue/text()[. = "Fever"]]') = 1

To include EMRValue1 in the select list:

select d.*, T.c.value('text()[1]', 'varchar(200)') EMRValue1
from [TABLE] d
    cross apply d.XML.nodes('/Data/COLRowData/DORowData/EMRValue1') T(c)
where d.XML.exist(...) = 1

note, that number of rows returned will inrease, because of multiple EMRValue1 elements in XML data for each row

edited Jul 15, 2013 at 14:50

answered Jul 8, 2013 at 20:10

i-one

5,1201 gold badge33 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2561997 Over a year ago

How can i include the "EMRValue1" in my Select statement from the XML Node which is satisfying the Where clause?

user2561997 Over a year ago

But since my where clause is satisfying data from this block only <DORowData> <PrimaryValue>Fever</PrimaryValue> <DataRowType>Present</DataRowType> <EMRValue1> 103 <EMRValue1 /> <HasPositiveValue>True</HasPositiveValue> </DORowData> The EMRValue1 in the Select clause should only return 1 row with the EMRValue1 = 103

i-one Over a year ago

In this case do following:

select d.XML, T.c.query('.') DORowDataXML, T.c.value('(EMRValue1/text())[1]', 'varchar(200)') EMRValue1 from [TABLE] d cross apply d.XML.nodes('/Data/COLRowData/DORowData') T(c) where T.c.exist('.[DataRowType/text()[. = "Present"] and PrimaryValue/text()[. = "Fever"]]') = 1

user2561997 Over a year ago

This works just perfectly barring one issue. If the XML Block in which am querying has 3 diff Blocks of 'DORowData', then I am getting 3 rows as result with EMRValue1 from all 3, where as i would have just wanted one value i.e 103.

i-one Over a year ago

Do you mean, that you query data, where there are three blocks satisfying condition "Present" and "Fever" and you need only one of them?

Collectives™ on Stack Overflow

XML Query within Multiple Rows of Data

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related