Querying SQL Server 2005 for certain XML values

Question

I am trying to query XML columns in SQL Server 2005

I have a really long string which is stored in a column and it is XML. I want to check if the visibility of a certain item is set to true or not.

Any idea on how to do this?

I can't post the string that has all the XML because this stack editor doesn't like the code.

Thanks

I have attached a picture showing my select statement and the results, you can see from the case expressions what I am trying to do.

enter image description here

Problem is the settings column shows the name of the column but I want to see if the visibility of that column e.g. (colFirstChoiceVendorPaymentTerms) is set to true or not...

If you post code, XML or data samples, just simply highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Nov 22 '13 at 11:24

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

Answer to your question depends heavily on xml schema you're using. For example, if you're using attributes xml like this:

'<Item ID="1" Visibility="1" />'

You can filter items with this query:

select *
from temp
where data.exist('Item[@Visibility = "1"]') = 1

See this on sql fiddle demo

It's hard to answer more precise until we don't have exact schema of your xml.

update

So it looks like you have DevExpress settings xml stored inside your db. So if your simplified xml is like this:

<XtraSerializer version="1.0" application="View">
  <property name="Columns" iskey="true" value="9">
    <property name="colFirstChoiceVendorPaymentTerms" isnull="true" iskey="true">
      <property name="Visible">true</property>
    </property>
    <property name="colSecondChoiceVendorPaymentTerms" isnull="true" iskey="true">
      <property name="Visible">false</property>
    </property>
  </property>
</XtraSerializer>

Then you can query it like:

select
data.value('(XtraSerializer/property/property[@name="colFirstChoiceVendorPaymentTerms"]/property[@name="Visible"])[1]', 'bit') as first,

data.value('(XtraSerializer/property/property[@name="colSecondChoiceVendorPaymentTerms"]/property[@name="Visible"])[1]', 'bit') as second
from temp

sql fiddle demo

update 2

if your data type is text/varchar, try this:

select
    c.data_xml.value('(XtraSerializer/property/property[@name="colFirstChoiceVendorPaymentTerms"]/property[@name="Visible"])[1]', 'bit') as first,
    c.data_xml.value('(XtraSerializer/property/property[@name="colSecondChoiceVendorPaymentTerms"]/property[@name="Visible"])[1]', 'bit') as second
from temp as t
    outer apply (select cast(t.data as xml) as data_xml) as c

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 22 '13 at 11:01

Roman Pekar

107,110
28
195
197

true Value 6 32 colFirstChoiceVendorPaymentTerms – jeffry Nov 22 '13 at 11:02
this is part of the XML. the property name is colFirstChoiceVendorPaymentTerms, im checking if the visibility is set to true or not.. in this case it is true – jeffry Nov 22 '13 at 11:03
thank you for your help but how would i add this to my case statement? – jeffry Nov 22 '13 at 11:14
why do you need case statement? I think you just need 1/0 for columns first/second/third – Roman Pekar Nov 22 '13 at 11:16
i seem to be getting errors because i am casting the column names from a table on a different server. data.value = data is the column name i assume? – jeffry Nov 22 '13 at 11:24
yes i see, but it throws error: Msg 258, Level 15, State 1, Line 2 Cannot call methods on text. – jeffry Nov 22 '13 at 11:30
ok i have uploaded the XML here: txt.do/dev1 you will see that the visibility for colFirstChoiceVendorPaymentTerms and colSecondChoiceVendorPaymentTerms is set to true. but there is no visibility for colThirdChoiceVendorPaymentTerms. this is what i want to check. if visibility is there or not. thank you again for your help. – jeffry Nov 22 '13 at 11:51
my last query should do it, try it on your db – Roman Pekar Nov 22 '13 at 12:07

evhen14 · Answer 2 · 2013-11-22T11:39:57.350

0

Try this code:

SELECT 
  CAST(data as XML).exist('//XtraSerializer//property[@name="colFirstChoiceVendorPaymentTerms"]/property[@name=''Visible'' and text()=''true'']') as col1,
  CAST(data as XML).exist('//XtraSerializer//property[@name="colSecondChoiceVendorPaymentTerms"]/property[@name=''Visible'' and text()=''true'']') as col2
  FROM TBL

edited Nov 22 '13 at 11:39

answered Nov 22 '13 at 11:08

evhen14

1,839
12
16

thanks but this returns a lot of data, i need the result to be specific to a column in the XML – jeffry Nov 22 '13 at 11:28
is it possible to get the columns to say 1 for true and 0 for false? – jeffry Nov 22 '13 at 11:40
edited once again, but without XML it's hard to help, I don't have data to test it. – evhen14 Nov 22 '13 at 11:41

Querying SQL Server 2005 for certain XML values

2 Answers2

update

update 2