querying using two attributes in xml column

Question

I have seen some examples querying using a single attribute but am puzzled on how to use 2 attributes.

<componentcache>
  <component Name="CALC_TODAYDATELONG" value="MONDAY, MAY 06, 2013" />
  <component Name="CALC_OFFICENAME" value="DEFAULT OFFICE" />
  <component Name="STAFFINFO_FULLNAME" value="LEE LEE, JR" />
  <component Name="PATINFO_FULLNAME" value="JAYNE H DOE" />
  <component Name="PATINFO_BIRTHDATE" value="11/07/1901" />
  <component Name="PATINFO_PATIENTNO" value="AG000003" />  
  <component Name="ENCOUNT_DXDESC1" value="ABC" />
  <component Name="ENCOUNT_DXDESC2" value="DEF" />
  <component Name="ENCOUNT_DXDESC3" value="HIJK" />
</componentcache>

SELECT DocumentStoreID, DocTemplateID, PersonID, Document from DocumentStore
WHERE DataCache.value('/componentcache/component...

I want to select the row(s) where Name like "ENCOUNT_DXDESC%" and value = 'DEF'

marc_s · Accepted Answer · 2013-07-24 04:52:36Z

2

Try something like this using a CTE (Common Table Expression):

;WITH XmlDataValues AS
(
    SELECT 
        DocumentStoreID,
        CompName = XComp.value('@Name', 'varchar(50)'),
        CompValue = XComp.value('@value', 'varchar(50)')
    from 
        DocumentStore
    CROSS APPLY
        DataCache.nodes('/componentcache/component') XTbl(XComp)  
)   
SELECT * 
FROM XmlDataValues
WHERE CompName LIKE 'ENCOUNT%'
AND CompValue = 'DEF'

The CTE basically takes every row in DocumentStore and gets a list of all <component> XML nodes (as XML) and extracts Name and value attributes from those XML nodes. The CTE then surfaces this information like a relational table - with column names CompName and CompValue. You can easily select from the CTE and use normal T-SQL statements and conditions for that

answered Jul 24, 2013 at 4:52

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

1 Comment

Gina Marano Over a year ago

I like this answer as well but it is a bit much for what I want to do but may be of use in the future!

Stuart Ainsworth · Accepted Answer · 2013-07-24 05:05:56Z

1

Here's a method using XQuery:

SELECT DocumentStoreID, DocTemplateID, PersonID, Document from DocumentStore
WHERE DataCache.exist('/componentcache/component[contains(@Name, "ENCOUNT_DXDESC")][@value="DEF"]')=1

This will return all rows in your table which contain an XML document with a node that has a Name attribute containing "ENCOUNT_DXDESC" and a value attribute of "DEF"

answered Jul 24, 2013 at 5:05

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

2 Comments

Gina Marano Over a year ago

Anyway to have a contains or like here "[@value="DEF"]"?

Stuart Ainsworth Over a year ago

yep; just replace the [@value="Def"] with [contains(@value, "DEF")]

Collectives™ on Stack Overflow

querying using two attributes in xml column

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related