querying xml data

i have the following xml file : How can get the data similar to this query Select Projects.*, Job.*, Task.* from Task inner join job on task.task_job_id = job.job_id inner join projects on job.job_proj_id = projects.proj_id where proj_id = 1 and job_id = 1 Is something of this sort possible by changing the structure of this xml structure? Is there a way to sort the data in desired manner?? ==================TaskManager.xml============================ 3 Job Tracking System JobTracker 4 28 Marketing collateral Marketing collateral and design jobs 9 1 3 Project Plan The MS Project based plan that all Incompass and FVB staff will follow. 6 2 28 Template Designs Approved (by FVB) template designs 6 1 1 Create Project Plan Approved (by FVB) template designs 7 3 2 Create initial designs 7 =====================================================

Re: querying xml data

Your query does not reflect your data. Do you want to find the project with project code="1" and find all the jobs that have a job code ="1"? What is your intent with the data? Do you want to use XSLT to transform the XML into something else, or do you want to just find the nodes to process them through some other business logic? Here is an XSLT stylesheet that gets the project with the specified project code, and then gets the jobs for the same project code.

Job

Re: querying xml data

sorry, the query i am looking for is like this Select Projects.*, Job.*, Task.* from Task inner join job on task.task_job_id = job.job_id inner join projects on job.job_proj_id = projects.proj_id where task_id = 1 Infact i am looking for a particular task, i.e. task_code = 1, then i want to display all the info related to that task, i.e. what job it is related to and inturn what project that job is related to.i want to create a list like this: Project Job Task Description Status Date Created ======================================================== Project Name Job Name Task Name Task Desc Task Status Task date Created

Re: querying xml data

If the response I posted does not fit your solution, can you please explain further your problem set? The query itself is not very important except that it demonstrates that you want some columns for a given condition. The XSLT stylesheet I provided does something similar: does this not fit your problem set? XML does not support SQL. You can use Microsoft's XQuery implementation for similar functionality, but it is not direct SQL syntax, either. Are you querying XML, or can you query a database? Can you insert the XML into a database and use SQL through the database instead?

Re: querying xml data

Hi Kaevans, Thanks for your efforts to help me sort out this problem. The problem domain is as follows: We have an intranet database, that is keeping record of all the client projects, which is being maintained exclusively by us, to keep track of jobs and tasks within those projects. Now for client reporting, we will extract the data in the form of xml files and upload onto our public website. The client reporting program will need to read the xml files and present the data in form of web pages. e.g. if i know that the client who has logged in has access to project 1,3 and jobs 2,4,7 within that project. then i should be able to extract the data for the above parameters from the xml file and present it. Since the client reporting programs are still in beta, if you can suggest, the best way to create the xml data, so that it is easy to query the data on the website later. if you need more input, please write back. thanks

Re: querying xml data

It seems like your data has a direct depency relationship, ie: a task belongs to one job, and a job belongs to one Project. It might be easier on you to structure the data something like:

This would make querying the data much simpler, since the relationships are implied through the document structure. If this is not the case, then your current structure is more important. However, I would still imply the relationship through ref IDs:

This will at leat help imply the relationships more tightly and conveys visually the dependence on other elements.

Re: querying xml data

if the structure of the xml file is : Then how do i make a query to findout all those records where job_id = 6 or job_id = 4

Re: querying xml data

You can use XPath.

/TaskManager/Project/Job[@id='4']

This asks the parser to start with the root "TaskManager" element, which also should be the first element in the document because we used the "/" preceding the element name. The TaskManager element should have a child element "Project", whose identity is insignificant... the only criteria is that TaskManager have a child element Project. Similarly, Project must have a child element "Job". The criteria you are specifying (kind of like a WHERE clause in SQL) is that the Job element must have an "id" attribute with value 4. If all of these criteria in the path are met, then a pointer to the "Job" element node is returned. From there, you can access its child elements or any descendant, or even ask it for its parent or ancestors. XPath is a very powerful language, but requires breaking out of the SQL mindset of returning tabular resultsets. XPath returns pointers to nodes.