SQLTeam.com | Weblogs | Forums

XML and SQL Query Unable to Get This!

Transact-SQL
#1

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:

#2

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.

#3

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">
or
<v:IsConditional>true</v:IsConditional>

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!
![SampleXML|690x239](upload://qmIlXKgf01nzI191qEu4F6rnGzt.jpeg)
#4

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

The following tick

`

<xml><yadi>
#5

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

#6

do you see this tick mark ` on your keyboard?

image

you need three of those before and after the xml body