Hello,

i am trying to read values from an xml variable. In this i have the data
from some records. And the records can (but must not) contain at first
position a field called 'id'. And i want the value from 'id', if field
exists.

Here a short example to show what i mean:
----------------------------------------------------------------
declare @pers xml, @tmp xml, @cnt int, @x int, @id int

set @pers = (select top 5 * from persons for xml raw, elements)
set @x = 1

select @cnt = @pers.value('data(count(/*))','int')
while @x <= @cnt begin
   select @tmp = @pers.query('/row[position()=sql:variable("@x")]/ID')
   select @id = @tmp.value('data(/ID)[1]','int')
   -- do something with id ...
  set @x = @x + 1
end
--------------------------------------------------------------
Okay, if there is no element 'id' in @pers, then @id should be NULL. That
is not the problem. But i don't know if 'id' is written as 'id' or 'ID' or
'Id' - in my example i get the value only if the element is 'ID', in all
other cases i get NULL as result. How to make element name case
insensitive?
And by the way - can i get the value direct from @pers without using @tmp?


thanks, Helmut

Helmut Woess (use***@inode.at) writes:
Join the appropriate standads committee and convince people that XML
should be made case-insensitive, I suppose.

XML is case-sensitive and that's the way it is. It appears to me that if
you don't know the column names, you are in dire straits anyway.

Yes:

   declare @pers xml, @tmp xml, @cnt int, @x int, @id int

   set @pers = (select top 5 * from Products for xml raw, elements)
   set @x = 1

   select @cnt = @pers.value('data(count(/*))','int')
   while @x <= @cnt begin
      select @id = @pers.value(
                '(/row[position()=sql:variable("@x")]/ProductID)[1]', 'int')
      PRINT @id
      set @x = @x + 1
   end

I don't really know what you use @id for, but if you are using it a
query, you should probably use nodex() instead, rather than using a
loop.

Am Sat, 22 Jul 2006 12:18:50 +0000 (UTC) schrieb Erland Sommarskog:

No, i don't want to chance this, it is okay. But sometimes there is a need
for case-insensitivity. There is a lower-case() function in XML, but XQuery
doesn't support all functions and i found no way.

I create the tables and most of them have an identity field on first
position, called id, sometimes i write 'id', sometimes 'ID',
And i want a more tolerant function where it doesn't matter if fieldname is
in uppercase or lowercase. Yes, i can check all tables and make 'id' equal
in all tables, but i would prefer the other way.
For a Un*x user or C# programmer it is normal to take care, but i am
working for years only with Delphi(pascal) and TSQL, where this doesn't
matter and so i am a little bit lazy in this case.

Thanks, i will try your tips.

bye, Helmut

Helmut Woess (use***@inode.at) writes:
I strongly recommend that you use case-sensitive collations for development
databases. Case-sensitive may not be what you want in the production. But
if the customer demands case-sensitive, and you have developed under a
case-insensitive collation you have a nightmare to sort out.

Under a case-sensitive collation, "id" and "ID" are distinct column names.

Am Mon, 24 Jul 2006 14:00:05 +0000 (UTC) schrieb Erland Sommarskog:

Interesting, i never tried this. But happily my application has to be
case-insensitive.

thanks, Helmut

datetime filtering
Combining 2 select statements
column dataType
Applying a function to a subquery
Case statement question