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 =====================================================
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.
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
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?
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
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.
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.
vnanchahal@y...
Member
117 Points
24 Posts
querying xml data
Sep 03, 2003 04:30 AM|LINK
kaevans
Participant
1150 Points
230 Posts
Microsoft
Re: querying xml data
Sep 03, 2003 12:58 PM|LINK
vnanchahal@y...
Member
117 Points
24 Posts
Re: querying xml data
Sep 03, 2003 11:40 PM|LINK
kaevans
Participant
1150 Points
230 Posts
Microsoft
Re: querying xml data
Sep 04, 2003 01:32 PM|LINK
vnanchahal@y...
Member
117 Points
24 Posts
Re: querying xml data
Sep 04, 2003 11:16 PM|LINK
kaevans
Participant
1150 Points
230 Posts
Microsoft
Re: querying xml data
Sep 05, 2003 12:31 AM|LINK
vnanchahal@y...
Member
117 Points
24 Posts
Re: querying xml data
Sep 05, 2003 05:25 AM|LINK
kaevans
Participant
1150 Points
230 Posts
Microsoft
Re: querying xml data
Sep 05, 2003 11:16 AM|LINK