stuck on XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

stuck on XML

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-23 : 13:27:37

Hi,

Been spinning on this for whole weekend I can't seem to get what you I want. I have the following xml result from my query. As you notice one of the child elements has the tag identifier VJobs, how can I make it so it says 'task' instead?

<task id="2" name="Saw 1" color="#99ccff" expand="true" />
<task id="3" name="Saw 2" color="#99ccff" expand="true" />
<VJobs id="3" name="Layout#" color="#99ccff">
<customproperty taskproperty-id="tpc0" value="Unknown" />
<customproperty taskproperty-id="tpc1" value="17.938 " />
<customproperty taskproperty-id="tpc2" value="Unknown" />
<customproperty taskproperty-id="tpc3" value="0" />
<customproperty taskproperty-id="tpc4" value="Operator Unknown" />
</VJobs>
</task>

Here is the query, which details jobs to be done on different equipments
SELECT EquipmentID + 1 as id,
EquipmentDescr as [name],
'#99ccff' AS color,
'true' AS [expand],
(SELECT JobID + 2AS id,
'Layout#' AS [name],
'#99ccff' AS color,
(SELECT [taskproperty-id] AS [taskproperty-id],
[value] AS [value]
FROM dbo.JobDetails customproperty
WHERE customproperty.JobID = VJobs.JobID
FOR XML AUTO, TYPE)
FROM VJobs
WHERE VJobs.EquipmentID = task.EquipmentID
FOR XML AUTO, TYPE)
FROM VEquipments task
ORDER BY EquipmentDescr
FOR XML AUTO, TYPE

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-23 : 13:43:25

Couldn't you just alias the table
FROM Vjobs AS Task?

Jim

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-23 : 14:08:20

I could but problem is VEquipments is already aliased as 'task'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 14:12:50

Does it error when you do as Jim suggested?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-23 : 14:25:36

No it does not error but it returns 'extra ' records because the inner most WHERE cclause

WHERE customproperty.JobID = VJobs.JobID
FOR XML AUTO, TYPE

would have to say WHERE customproperty.JobID = task.JobID which brings in extra records it is as if i was doing a CROSS JOIN

Thank you

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-23 : 15:01:47

Yes, sorry. I looked for that but didn't see it.

Jim

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-24 : 11:04:16

HI there I posted this on the weekend maybe I will try again . anybody can help me out with this please?
Hi,

Been spinning on this for whole weekend I can't seem to get what you I want. I have the following xml result from my query. As you notice one of the child elements has the tag identifier VJobs, how can I make it so it says 'task' instead?

<task id="2" name="Saw 1" color="#99ccff" expand="true" />
<task id="3" name="Saw 2" color="#99ccff" expand="true" />
<VJobs id="3" name="Layout#" color="#99ccff">
<customproperty taskproperty-id="tpc0" value="Unknown" />
<customproperty taskproperty-id="tpc1" value="17.938 " />
<customproperty taskproperty-id="tpc2" value="Unknown" />
<customproperty taskproperty-id="tpc3" value="0" />
<customproperty taskproperty-id="tpc4" value="Operator Unknown" />
</VJobs>
</task>

Here is the query, which details jobs to be done on different equipments
SELECT EquipmentID + 1 as id,
EquipmentDescr as [name],
'#99ccff' AS color,
'true' AS [expand],
(SELECT JobID + 2AS id,
'Layout#' AS [name],
'#99ccff' AS color,
(SELECT [taskproperty-id] AS [taskproperty-id],
[value] AS [value]
FROM dbo.JobDetails customproperty
WHERE customproperty.JobID = VJobs.JobID
FOR XML AUTO, TYPE)
FROM VJobs
WHERE VJobs.EquipmentID = task.EquipmentID
FOR XML AUTO, TYPE)
FROM VEquipments task
ORDER BY EquipmentDescr
FOR XML AUTO, TYPE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources