question 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)

question on XML

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-19 : 12:18:47

Hi

I am trying to get the following xml result to feed to our GanttProject Gantt chart.

<task id="0" name="Saw 1" color="#99ccff" expand="true">
<task id="9" name="Layout#" color="#99ccff">
<customproperty taskproperty-id="tpc0" value="2008-06-19T10:15:36.45Z"/>
<customproperty taskproperty-id="tpc1" value="2008-06-19T10:16:05.29Z"/>
<customproperty taskproperty-id="tpc2" value="0"/>
<customproperty taskproperty-id="tpc3" value="true"/>
<customproperty taskproperty-id="tpc4" value="McGraw"/>
</task>
</task>

The values tpc(n) come from columns of the same row. How can I do this dynamically instead of having 5 select joined together by UNION? Is there a better way?

SELECT 'tpc0' AS [taskproperty-id], StartDateTime AS [value]
FROM VJobs customproperty
UNION
--Expected Process Time
SELECT 'tpc1' AS [taskproperty-id], ExpProcessTime AS [value]
FROM VJobs customproperty
INNER JOIN VOptimization vo
ON (customproperty.TagNumber = vo.TagNumber AND customproperty.LayoutNumber = vo.OptimizationID)
UNION
--Time elapsed
SELECT 'tpc2' AS [taskproperty-id],
FROM VJobs customproperty
UNION
--Status
SELECT 'tpc3' AS [taskproperty-id],
FROM VJobs customproperty
UNION
--Operator Name
SELECT 'tpc4' AS [taskproperty-id],
FROM VJobs customproperty
FOR XML AUTO, TYPE

Thanksml

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:21:53

Why are using hardcoded values and still refering to tables?

you could simply use

SELECT 'tpc2' AS [taskproperty-id]

instead of

SELECT 'tpc2' AS [taskproperty-id],
FROM VJobs customproperty

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-19 : 12:27:54

I need to refer to tables in order to return the actual values for that element in the xml. oops sorry I see what you mean. Not cleaned up I was just using it as an example here it is the finished product

SELECT 'tpc0' AS [taskproperty-id], StartDateTime AS [value]
FROM VJobs customproperty
UNION
--Expected Process Time
SELECT 'tpc1' AS [taskproperty-id], ExpProcessTime AS [value]
FROM VJobs customproperty
INNER JOIN VOptimization vo
ON (customproperty.TagNumber = vo.TagNumber AND customproperty.LayoutNumber = vo.OptimizationID)
UNION
--Time elapsed
SELECT 'tpc2' AS [taskproperty-id], GEDATE() - StartDateTime AS [value]
FROM VJobs customproperty
UNION
--Status
SELECT 'tpc3' AS [taskproperty-id],
CASE
WHEN EndDateTime IS NULL THEN 0
ELSE 1
END AS [value]
FROM VJobs customproperty
UNION
--Operator Name
SELECT 'tpc4' AS [taskproperty-id], OperatorFirstName + ' ' + OperatorLastName AS [value]
FROM VJobs customproperty
INNER JOIN VJobOperators vjo
ON customproperty.JobID = vjo.JobID
INNER JOIN VOperators vo
ON vjo.OperatorID = vo.OperatorID
FOR XML AUTO, TYPE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:36:28

Ok. But guess you cant make this dynamic as anyways you have some hardcoded a values apperaing and also has some other tables also involved in between. I think you could just structure it like this for more clarity:-

SELECT [taskproperty-id],[value]
FROM
(
SELECT 'tpc0' AS [taskproperty-id], StartDateTime AS [value] 
FROM VJobs customproperty
UNION
--Expected Process Time
SELECT 'tpc1' AS [taskproperty-id], ExpProcessTime AS [value] 
FROM VJobs customproperty
INNER JOIN VOptimization vo
ON (customproperty.TagNumber = vo.TagNumber AND customproperty.LayoutNumber = vo.OptimizationID)
UNION
--Time elapsed
SELECT 'tpc2' AS [taskproperty-id], GEDATE() - StartDateTime AS [value]
FROM VJobs customproperty
UNION
--Status
SELECT 'tpc3' AS [taskproperty-id], 
CASE 
WHEN EndDateTime IS NULL THEN 0
ELSE 1
END AS [value]
FROM VJobs customproperty
UNION
--Operator Name
SELECT 'tpc4' AS [taskproperty-id], OperatorFirstName + ' ' + OperatorLastName AS [value]
FROM VJobs customproperty
INNER JOIN VJobOperators vjo
ON customproperty.JobID = vjo.JobID
INNER JOIN VOperators vo
ON vjo.OperatorID = vo.OperatorID)t
FOR XML AUTO, TYPE

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-19 : 13:08:29

Thank you very much! Looks much cleaner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources