Query XML contents In SQL Server 2000

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » General » Query XML contents In SQL Server 2000

Query XML contents In SQL Server 2000

Rate Topic

Display Mode

Topic Options

Author

Message

Zeeshan Haider

Posted Thursday, November 19, 2009 3:48 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 01, 2009 12:48 PM
Points: 1, Visits: 39

I am using the SQL Server 2000 as RDBMS.. I am tasked to develop an application which is supposed to handle user's comments/notes on many different web sites. Each site has its own specific data which uniquely identify the notes/comments entered on it. So, i must store that specific data in my database's table. The problem is that the site specific data is undefined. It could be anything.So, i am not aware about the structure of the specific data. I decided to keep the site specific data into XML. So, I developed a table like below,

NoteID INT
Note VARCHAR(1000)
ApplicationID VARCHAR(10)
UserID VARCHAR(10)
CreateDate DATETIME
ModifiedDate DATETIME
Parameters VARCHAR(1000)

Parameters column actually stores the site specific data in form of XML e.g. '<parameters><profileid>1796</profileid><state>page2</state></parameters>'........

I would pass a parameter to my stored procedure from my application. This parameter enloses the search criteria for the site specific data in form of XML too. For example, I would pass '<parameters><profileid>0001</profileid><state>unknown</state></parameters>' . Rest of the parameters like ApplicationID would pass in a standard way like we do in normal cases. :) So, enough explanation i think, lets come to the point,

How could i query the specific element e.g. profileid, within stored procedure by following the above architecture so that application must be able to render the correct notes/comments on the client? Any help would be highly appreciated.

Thanks.

Post #821998

« Prev Topic | Next Topic »

Permissions