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
 General SQL Server Forums
 New to SQL Server Programming
 for xml explicit

Author  Topic 

raroskar
Starting Member

2 Posts
Posted - 2010-03-08 : 17:06:38
I am trying to replicate an xml from an existing xml using "for xml explicit" .But I am not getting the exact xml from second node onwards.Atttached is the part of my "select" statements that i wrote to get till "description" node.

Problem: The description node comes in the last task node rather than coming for each task individually

Any help is appreciated.

declare @tasks table
(
TasksCount int ,
TaskOID uniqueidentifier ,
TaskName nvarchar(70) ,
startDate datetime ,
endDate datetime
)

declare @tempDescription table
(
TasksCount int ,
TaskOID uniqueidentifier ,
TaskName nvarchar(70) ,
description nvarchar(70)
)


declare @tasksList xml
set @tasksList = '<TaskCollection>
<tasks count="20">
<task oid="3b864520-8fb9-471d-8432-9a3565ad670f" name="Task 1" startDate="1970-01-01" endDate="1970-01-01">
<description><![CDATA[]]></description>
<activities count="10">
<activity oid="b1a72cbf-8b4e-49af-bfc8-6a4b2e0fc3a6" name="Client Travel" code="CLNTRAV" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="3ceb57da-6a48-4e3a-b5cf-4491d1ce72c8" name="Consulting" code="CONS" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="fe03a275-e777-4983-8fcd-0b0a238387f1" name="Consulting - Weekend" code="CONSWEND" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="3f722abb-3a0c-4cd8-8f1b-439d8ef8ff3d" name="Consulting-After Hours" code="CONSAFTHR" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="f042f6ae-6ed0-478c-ac87-d82f0f8564b2" name="Development" code="DEV" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="c8621a55-4f2d-434c-baee-b22bfccce00c" name="Pre-Sales/Sales" code="PRESALE" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="d27a4f33-9f52-45b0-8136-055164ada5ef" name="Prod Supprt-Cust Serv Requst" code="PRDSUPPCS" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="928e1f26-bdad-4a56-9911-6ed1e9641e0f" name="Prod Supprt-PM Apprvd" code="PRDSUPPPM" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="c9449c95-1ef8-4ed2-87ca-55f0fe1595a1" name="Project Supprt (Not Prod)" code="PROJSUPPNP" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="ad945434-0d0b-4dc2-b021-c7df6e1119d4" name="Training-Client" code="TRAINCLN" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
</activities>
<defaultRates count="1">
<defaultRate oid="4c33ae0e-e7ee-40ac-86ed-15b4da2cb817" minStep="0.00" maxStep="24.00">
<rateType oid="0370a010-c845-47b4-998f-c8f34c8d0d03" />
</defaultRate>
</defaultRates>
</task>
<task oid="11921b16-3347-454f-99cc-2add3674129f" name="Task 3" startDate="1970-01-01" endDate="1970-01-01">
<description><![CDATA[]]></description>
<activities count="10">
<activity oid="b1a72cbf-8b4e-49af-bfc8-6a4b2e0fc3a6" name="Client Travel" code="CLNTRAV" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="3ceb57da-6a48-4e3a-b5cf-4491d1ce72c8" name="Consulting" code="CONS" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="fe03a275-e777-4983-8fcd-0b0a238387f1" name="Consulting - Weekend" code="CONSWEND" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="3f722abb-3a0c-4cd8-8f1b-439d8ef8ff3d" name="Consulting-After Hours" code="CONSAFTHR" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="f042f6ae-6ed0-478c-ac87-d82f0f8564b2" name="Development" code="DEV" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="c8621a55-4f2d-434c-baee-b22bfccce00c" name="Pre-Sales/Sales" code="PRESALE" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="d27a4f33-9f52-45b0-8136-055164ada5ef" name="Prod Supprt-Cust Serv Requst" code="PRDSUPPCS" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="928e1f26-bdad-4a56-9911-6ed1e9641e0f" name="Prod Supprt-PM Apprvd" code="PRDSUPPPM" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="c9449c95-1ef8-4ed2-87ca-55f0fe1595a1" name="Project Supprt (Not Prod)" code="PROJSUPPNP" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
<activity oid="ad945434-0d0b-4dc2-b021-c7df6e1119d4" name="Training-Client" code="TRAINCLN" billable="True" allowBillable="True">
<assignments count="0" />
</activity>
</activities>
<defaultRates count="1">
<defaultRate oid="ceb8d7b0-b57a-4516-bdd9-d046a13c644e" minStep="0.00" maxStep="24.00">
<rateType oid="0370a010-c845-47b4-998f-c8f34c8d0d03" />
</defaultRate>
</defaultRates>
</task>
</tasks>
</TaskCollection>'

declare @iDoc int
Exec sp_xml_preparedocument @iDoc OUTPUT,@tasksList;


insert into @tasks(TasksCount,TaskOID,TaskName,startDate,endDate)
Select
* from OpenXml(@iDoc,'/TaskCollection/tasks/task')
With (TasksCount int '../@count',
TaskOID uniqueidentifier '@oid',
TaskName nvarchar(70) '@name',
startDate datetime '@startDate',
endDate datetime '@endDate'

)

insert into @tempDescription(TasksCount,TaskOID,description)
Select
* from OpenXml(@iDoc,'/TaskCollection/tasks/task/description')
With (TasksCount int '../../@count',
TaskOID uniqueidentifier '../@oid',
TaskName nvarchar(70) '../@name'
)




--
select 1 as Tag, -- TaskCollection
NULL as parent,
NULL as 'TaskCollection!1',
NULL as 'tasks!2!count',
NULL as 'task!3!oid',
NULL as 'task!3!name',
NULL as 'task!3!startDate',
NULL as 'task!3!endDate',
NULL as 'description!4!!CDATA'
union
select top 1 2 as Tag, -- Task Count
1 as Parent,
NULL as 'TaskCollection!1',
TasksCount as 'tasks!2!count',
NULL as 'task!3!oid',
NULL as 'task!3!name',
NULL as 'task!3!startDate',
NULL as 'task!3!endDate',
NULL as 'description!4!!CDATA'
from @tasks
union
select distinct 3 as Tag, 2 as parent, --Task
NULL as 'TaskCollection!1',
TasksCount as 'tasks!2!count',
taskoid 'task!3!oid',
TaskName 'task!3!name',
startDate 'task!3!startDate',
endDate 'task!3!endDate',
NULL as 'description!4!!CDATA'
from @tasks
union
select distinct 4 as Tag, 3 as parent, --Description
NULL as 'TaskCollection!1',
tp.TasksCount as 'tasks!2!count',
tp.taskoid 'task!3!oid',
tp.TaskName 'task!3!name',
startDate 'task!3!startDate',
endDate 'task!3!endDate',
ts.description as 'description!4!!CDATA'
from @tasks tp,@tempDescription ts
where tp.taskoid = ts.taskoid
for xml explicit
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site