SQL 2005 to XML

[yosiasz]

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

[deepuv04]

Hi,
try as first take the xml output as string ( into @String) then do a replace function and then convert the string (@string) as xml.

DECLARE @String VARCHAR(MAX)
SET @String = ''
SET @String = CONVERT(VARCHAR(MAX),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)
 
SET @String = Replace(@String,'VJobs ','task')
 
SELECT convert(xml,@String)
 
 
 
 

Thanks

[yosiasz]

deepuv04

Ok is there a grammy award for tech guys cause you deserve one. It works great!!! Thank you so much!
I am using this for a GanttProject display, users are gonna love it.

Youhaev answered another question of mine..that is concatenating xml. That is now possible then since can convert to varchar, concatenate then convert to XML


YEE HAAAA thanks a lot!!!