SQLTeam.com | Weblogs | Forums

XML and SQL Query Unable to Get This!

I am really struggling with this and must get this somehow. Can anyone please help me.
I have a column in this table called "PropertyBagxml" and it contains XML data. There is one field and value in this XML file that I need to query on.
My query is going to say something like IF xml field value = True then "yes" else "no"
So here is the sample of the XML file:

  • <v:ConditionDelegate i:nil="true" />*
  • <v:CreatedBy>O'Neal, Charlton</v:CreatedBy>*
  • <v:CreatedWhenUTC xmlns:d1p1="... http link here...">*
  • d1p1:DateTime2022-07-15T15:51:52.07Z</d1p1:DateTime>*
  • d1p1:OffsetMinutes-240</d1p1:OffsetMinutes>*
  • </v:CreatedWhenUTC>*
  • <v:Height>79.5</v:Height>*
  • <v:IsConditional>false</v:IsConditional>*

The statement I need to query against is the one that states: <v:IsConditional>false</v:IsConditional>

Can someone help me with this, PLEASE!!

Thanks. First time posting on here so be nice :slight_smile:

You'll need to repost your XML example, it's not well-formed and the XML namespaces for v:, i: and d1p1 are missing. Make sure to use the preformatted text option for the next post, it's the </> icon in the toolbar.

Generally though, you should be able to do this:

SELECT CASE xmlColumn.value('(//v:IsConditional)[1]','varchar(5)') 
WHEN 'true' THEN 'yes' 
WHEN 'false' THEN 'no' END
FROM myTable
WHERE xmlColumn.exist('//v:IsConditional.text()')=1

If you are parsing multiple XML nodes for that value, you'll need to provide a more comprehensive XML sample. The snippet you already posted is a bit short as well.

Hi thanks for replying.
If I use the sample code you provided this is what I get:

Msg 2229, Level 16, State 1, Line 1
XQuery [SXAFBFormPart.PropertyBagxml.value()]: The name "v" does not denote a namespace.

I tried to include more of the XML yesterday but it said I couldn't include links. So... I'm going to try and attach it for you.

In the sample file I can either use:

<v:ConditionDelegate i:type="v:ConditionDelegate">

In the CASE statement. In other words if the field says v:ConditionDelegate THEN 'true' or IF v:IsConditional = true then 'yes'

Either would work in my case.

Thanks again!

Please post xml using the three ticks before and after the xml

The following tick



Hello. Did you have any luck with this? Just checking. I'm still not able to get this to work :frowning:

do you see this tick mark ` on your keyboard?


you need three of those before and after the xml body