XML and SQL2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am trying to design a system such that I can store all of a user's information as a XML string stored in a field in the database (SQL2000). Let's say the XML string has XML tags such as first name, last name, phone number, address etc. Is there a way to query the SQL database based on a XML tag? eg: Can I query the database saying, select user id for all users with last name = "Cooper" even though the last name of the user is embedded in the xml string field of the table? (assuming that user id and the xml string are fields in the database table

One way of doing this would be to read the xml string and then parse it. But when I have a large number of users, this could get very slow. Is there a better way of doing this

Thanks
-Divya
 
Hi Divya:

I'm just wondering, if you are using SQL Server 2000, why not use the native
support for XML... SELECT First_Name, Last_Name FROM SomeTable WHERE
Something = SomethingElse FOR XML AUTO ELEMENTS. You can then use the
XMLReader and do some pretty fancy stuff with it. The native support for
XML on the SQL Server side is pretty real and you can grab just about
anything client side once you have it.

If you really need to store each string in there and don't want to use the
XML features (BOL has some great examples) you can use something like WHERE
FieldName LIKE '<FieldName>%</FieldName>'

--
W.G. Ryan MVP Windows - Embedded

www.devbuzz.com
www.knowdotnet.com
http://www.msmvps.com/williamryan/
Divya said:
Hi,

I am trying to design a system such that I can store all of a user's
Click to expand...
information as a XML string stored in a field in the database (SQL2000).
Let's say the XML string has XML tags such as first name, last name, phone
number, address etc. Is there a way to query the SQL database based on a XML
tag? eg: Can I query the database saying, select user id for all users with
last name = "Cooper" even though the last name of the user is embedded in
the xml string field of the table? (assuming that user id and the xml string
are fields in the database table)
One way of doing this would be to read the xml string and then parse it.
Click to expand...
But when I have a large number of users, this could get very slow. Is there
a better way of doing this?
 
Hi,

No, you cannot query against XML strings in a SQL Server 2000. As I have
heard next version of the SQL Server will have this sort of capabilities.
 
The next version of Sql Server, as well as most of ADO.NET is just stuff
that's put out there just to drive me nuts ;-). The more I play with
ADO.NET 2.0 the more I can't wait for it to be here. Same for SQL Server
2005. Soo cool, so powerful, so feature rich...and yet so far off.

The XQueryProcessor/ XQuery seems like the made to order solution for his
problem. However, I haven't been able to figure out the syntax for
wildcards between the tags. I'm sure I'm just overlooking somethign but I
can't figure it out for the life of me (which almost always means its really
simple).

Have you had a chance to check ou the schema repository in SqlServer 05?
It's more a 05 feature than an ADO.NET 2.0 thing but it rocks. You can
define a schema but it works sort of like a stored proc. A bunch of
different users can make calls against these schemas so you can centralize
all of them and even add new ones during a user session. Don't seem to be
able to edit ones that are being used but that's not a huge deal.

Anyway, it's late and I'm babbling, I just saw the mention of the new XML
stuff and I always start nerding out when the subject comes out. It's
definitely going to be cool!

Cheers,

Bill

--
W.G. Ryan MVP Windows - Embedded

www.devbuzz.com
www.knowdotnet.com
http://www.msmvps.com/williamryan/
 
Back
Top