Query Empty node

Hello,

I have an XML file which contains different shops, where a certain node (<openinhours>) is empty in some occasions.

The node displays as <openinghours /> in my xml file in that case.

Now when I try to get the openinghour value of all the shops, it shows an error that I cannot atomize.

Is there any way to check if the node contains text by using the value or exist?

Thanks in advance!

I will try to clarify myself.

In my db, I have every shop in a different row, with columns: ShopID, ShopName, ShopXML. ShopXML contains the rest of the info of te shop, and is xml-field.

In that xml, i have something like this:

<shopinfo><openinghours>9a.m. - 18p.m.</openinghours> ...more xml ...</shopinfo>

However, in some cases this just is:

<shopinfo><openinghours /> ...more xml... </shopinfo>

I want to get all the openinghours where available from my xml (and I use .value),

but using

ShopXML.value('(/shopinfo/openinghours)[1]','varchar(max)') doesn't work because of the empty nodes.

How can I solve this?

Thank you!

|||

Thanks for providing more information. I'll have to ask some more questions before I can help you.

In the previous post you said that you got an error saying that you cannot atomize. Which is the exact error message that you get? I would assume that it is a static error, which means that the ShopXML is typed with an XML schema collection. Is this true? If it is, what's the type of openinghours element?

Thanks,

Adrian

|||

Yes I have a schema collection. The type is string.

When I disable the schemas it works fine. But if I choose not to use the schema's, will it affect the speed of querying data in my XML field?

|||

It's better to use the schema collection if you can.

I still don't understand what exactly doesn't work when you have a schema collection. Do you get an error? If yes, what error do you get and when? Or is it that you don't want to have empty openinghours in your result?

Regards,

Adrian