How to get Record comparing from XML attribute

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 2005 Forums

Transact-SQL (2005)

How to get Record comparing from XML attribute

Author

Topic

sadiqmodan
Starting Member

16 Posts

Posted - 2011-11-29 : 07:47:48

Hello,

I am storing XML in Content field of Node Table, XML format looks like as below.


<Answers>
  <AnswerSet>
    <Answer questionId="ProductCode">DLP3</Answer>
    <Answer questionId="ProductName">Pinhole 12V</Answer>
    <Answer questionId="Description"><![CDATA[The <strong>Pinhole</strong> trim has a very small aperture creating a dramatic vertical beam lighting effect ideally suited for lighting objects directly beneath it.</p>

<p>A narrow beam lamp is recommended.]]></Answer>
    <Answer questionId="CutOut">85mm</Answer>
    <Answer questionId="IPRating">IP20</Answer>
	 <Answer questionId="AdjustableAngle" />
    <Answer questionId="AirFlow" />
    <Answer questionId="Amperage" />
    <Answer questionId="ApertureDiameter" />
    <Answer questionId="ApparentPowerofHarmonicProducts" />
    <Answer questionId="ApparentRadiatedPower" />
	    <Answer questionId="ItemPrice">5.96</Answer>
    <Answer questionId="MainImageURL">DLP1side_test.jpg</Answer>
  </AnswerSet>
</Answers>

Above XML is stored for every node record. and every node have same format of xml but data is different.

Condition
I want to retrive records which have questionId="ProductCode" and it's value is DLP3 using Xquery.

Any one have best idea for this.

Thanks in Advance,
MohmedSadiq Modan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-29 : 08:05:52

Probably the most efficient way would be to use the xml function exist to check if the attribute value matches what you are looking for, as in:

SELECT 
   c.value('.','varchar(32)')
FROM
   YourTable yt
   CROSS APPLY xmlCol.nodes('//Answer') T(c)
WHERE
   c.exist('.[@questionId="ProductCode"]') = 1

sadiqmodan
Starting Member

16 Posts

Posted - 2011-11-29 : 08:28:00

Hi sunitabeck,

Thanks for quick response,

but I want result which has also node text DLP3.

For e.g. I have records like below

ID Content
=== =======
1 <Answers><AnswerSet><Answer questionId="ProductCode">DLP1</Answer>....</AnswerSet></Answers>
2 <Answers><AnswerSet><Answer questionId="ProductCode">DLP3</Answer>....</AnswerSet></Answers>
3 <Answers><AnswerSet><Answer questionId="ProductCode">DLP2</Answer>....</AnswerSet></Answers>

So using Query I need to get node ID 2 record

Please share your valuable knowledge.

Thanks,
Mohmedsadiq Modan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-29 : 09:49:47

You can add the second condition to the WHERE clause, for example either of these should work


WHERE
   c.exist('.[@questionId="ProductCode"]') = 1
   AND c.value('.','varchar(32)') = 'DLP3'


WHERE
   c.value('.[@questionId="ProductCode"][1]','varchar(32)') = 'DLP3'

If you want to get the node rather than the value, use c.query('.') as the column to query.

sadiqmodan
Starting Member

16 Posts

Posted - 2011-11-30 : 01:32:49

Hi sunitabeck,

Thanks for your help your solution is work for me. Thank you again.

Thanks,
Mohmedsadiq modan

sadiqmodan
Starting Member

16 Posts

Posted - 2011-12-02 : 06:10:38

Hello sunitabeck,

You solution is worked but when I have 2000+ records in DB then I face Performance issue in this query.

Please Suggest the better solution to achieve best performance

Thanks,
Mohmedsadiq Modan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 06:18:52

did you try adding XML index to speed up Xquery?

http://msdn.microsoft.com/en-us/library/ms345121(v=sql.90).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sadiqmodan
Starting Member

16 Posts

Posted - 2011-12-02 : 07:43:47

Hello visakh16,

Thanks for you help

Now Using index, Performance is increase.

Thanks you,
Mohmedsadiq Modan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 07:50:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources