By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,362 Members | 1,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,362 IT Pros & Developers. It's quick & easy.

SQL 2005 to XML

P: 3
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
Jun 24 '08 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
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.

Expand|Select|Wrap|Line Numbers
  1. DECLARE @String VARCHAR(MAX)
  2. SET @String = ''
  3. SET @String = CONVERT(VARCHAR(MAX),SELECT  EquipmentID + 1 as id,
  4.                                         EquipmentDescr as [name],
  5.                                         '#99ccff' AS color,
  6.                                         'true' AS [expand],
  7.                                         (SELECT JobID + 2AS id,
  8.                                                 'Layout#' AS [name],
  9.                                                 '#99ccff' AS color,
  10.                                         (SELECT [taskproperty-id] AS [taskproperty-id],
  11.                                                 [value] AS [value]
  12.                                         FROM dbo.JobDetails customproperty
  13.                                         WHERE customproperty.JobID = VJobs.JobID
  14.                                         FOR XML AUTO, TYPE)
  15.                                         FROM VJobs
  16.                                         WHERE VJobs.EquipmentID = task.EquipmentID
  17.                                         FOR XML AUTO, TYPE)
  18.                                 FROM VEquipments task
  19.                                 ORDER BY EquipmentDescr
  20.                                 FOR XML AUTO, TYPE)
  21.  
  22. SET @String = Replace(@String,'VJobs ','task')
  23.  
  24. SELECT convert(xml,@String)
  25.  
  26.  
  27.  
  28.  
Thanks
Jun 25 '08 #2

P: 3
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!!!
Jun 25 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.