none
for xml explicit

    General discussion

  • 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.

    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>
    </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

    Tuesday, March 09, 2010 11:00 PM

All replies

  • Hi DotNetLearner2,

    Could you give us the exactly result that you want?

    Regards,
    Tom Li - MSFT
    Thursday, March 11, 2010 7:18 AM
    Moderator
  • Hi,

    You have not mentioned code for activities.
    Update your code for this section and you would get the same xml

    Thanks,
    Mayur
    Thursday, March 11, 2010 8:15 AM
  • The result would be exactly the same as shown in the existing XML.

    Friday, March 12, 2010 3:47 AM
  • No, after writing the complete code also I am not getting the result I am looking for.
    For oneTask, it shows fine. But there is more data in XML. Total Tasks are 10 . inside each task there are 10 activities.
    I couldn't put the entire XML because of the word Limit


     

    select top 1 5 as Tag,

    3

    as parent,

    Same as last select statement


    activities
    as 'activities!5!count', --New

     

    NULL as 'activity!6!oid',

     

    NULL as 'activity!6!name',

     

    NULL as 'activity!6!Code',

     

    NULL as 'activity!6!Billable',

     

    NULL as 'activity!6!allowBillable',

     

    NULL as 'assignments!7!count',

     

    NULL as 'defaultRates!8!count',

     

    NULL as 'defaultRate!9!oid',

     

    NULL as 'defaultRate!9!minStep',

     

    NULL as 'defaultRate!9!maxStep',

     

    NULL as 'rateType!10!oid'

     

    from @tempactivities ta

     

    inner join @tasks tsk on tsk.taskoid = ta.taskoid

     

    inner join @tempDescription td on tsk.taskoid = td.taskoid

    union

     

     

    select distinct 6 as Tag,

    5

    as parent,

    activities

    as 'activities!5!count',

    ta

    .activityoid as 'activity!6!oid',

    ta

    .ActivityName as 'activity!6!name',

    ta

    .Code as 'activity!6!Code',

    ta

    .Billable as 'activity!6!billable',

    ta

    .allowBillable as 'activity!6!allowBillable',

     

    NULL as 'assignments!7!count',

     

    NULL as 'defaultRates!8!count',

     

    NULL as 'defaultRate!9!oid',

     

    NULL as 'defaultRate!9!minStep',

     

    NULL as 'defaultRate!9!maxStep',

     

    NULL as 'rateType!10!oid'

     

    from @tempactivities ta

     

    inner join @tasks tsk on tsk.taskoid = ta.taskoid

     

    inner join @tempDescription td on tsk.taskoid = td.taskoid

    union

     

     

    select distinct 7 as Tag,

    6

    as parent,

    ta

    .allowBillable as 'activity!6!allowBillable',

    ta

    .assignments as 'assignments!7!count',

     

    NULL as 'defaultRates!8!count',

     

    NULL as 'defaultRate!9!oid',

     

    NULL as 'defaultRate!9!minStep',

     

    NULL as 'defaultRate!9!maxStep',

     

    NULL as 'rateType!10!oid'

     

    from @tempactivities ta

     

    inner join @tasks tsk on tsk.taskoid = ta.taskoid

     

    inner join @tempDescription td on tsk.taskoid = td.taskoid

    union

     

     

    select distinct 8 as Tag,

    3

    as parent,

     

    NULL as 'activity!6!oid',

     

    NULL as 'activity!6!name',

     

    NULL as 'activity!6!Code',

     

    NULL as 'activity!6!billable',

     

    NULL as 'activity!6!allowBillable',

     

    NULL as 'assignments!7!count',

    dr

    .DefaultRatesCount as 'defaultRates!8!count', -- New Node

     

    NULL as 'defaultRate!9!oid',

     

    NULL as 'defaultRate!9!minStep',

     

    NULL as 'defaultRate!9!maxStep',

     

    NULL as 'rateType!10!oid'

     

    from @TempDefaultRates dr

     

    inner join @tasks tsk on tsk.taskoid = dr.taskoid

    union

     

     

    select distinct 9 as Tag,

    8

    as parent,

     

    same as last select
    NULL
    as 'activities!5!count',

     

    NULL as 'activity!6!oid',

     

    NULL as 'activity!6!name',

     

    NULL as 'activity!6!Code',

     

    NULL as 'activity!6!billable',

     

    NULL as 'activity!6!allowBillable',

     

    NULL as 'assignments!7!count',

    dr

    .DefaultRatesCount as 'defaultRates!8!count',

    dr

    .DefaultRateOID as 'defaultRate!9!oid',

    dr

    .minStep as 'defaultRate!9!minStep',

    dr

    .maxStep as 'defaultRate!9!maxStep',

     

    NULL as 'rateType!10!oid'

     

    from @TempDefaultRates dr

     

    inner join @tasks tsk on tsk.taskoid = dr.taskoid

    union

     

     

    select distinct 10 as Tag,

    9

    as parent,

    same as above select


    dr

    .DefaultRatesCount as 'defaultRates!8!count',

    dr

    .DefaultRateOID as 'defaultRate!9!oid',

    dr

    .minStep as 'defaultRate!9!minStep',

    dr

    .maxStep as 'defaultRate!9!maxStep',

    dr

    .rateTypeoid as 'rateType!10!oid'

     

    from @TempDefaultRates dr

     

    inner join @tasks tsk on tsk.taskoid = dr.taskoid

    for

     

    xml explicit

    Friday, March 12, 2010 4:03 AM
  • The result looks something like this.


    <

     

    task oid="8F09B424-D94F-4573-9F53-E9C820354FA0" name="Task 9" startDate="1970-01-01T00:00:00" endDate="1970-01-01T00:00:00" />

    <

     

    task oid="B231A1D8-09F9-4D93-82A1-FB7567E9B308" name="Task 4" startDate="1970-01-01T00:00:00" endDate="1970-01-01T00:00:00">

    <

     

    description><![CDATA[Task 2]]></description>

    <

     

    description><![CDATA[Task 10]]></description>

    <

     

    activities count="10">

    <

     

    activity oid="D27A4F33-9F52-45B0-8136-055164ADA5EF" name="Prod Supprt-Cust Serv Requst" Code="PRDSUPPCS" Billable="True" allowBillable="True" />

    <

     

    activity oid="F042F6AE-6ED0-478C-AC87-D82F0F8564B2" name="Development" Code="DEV" Billable="True" allowBillable="True">

    <

     

    assignments count="0" />

    </

     

    activity>

    </

     

    activities>

    <

     

    defaultRates count="1" />

    <

     

    defaultRates count="1">

    <

     

    defaultRate oid="B6B4211A-653E-4D6B-ADD6-58E89124843D" minStep="0" maxStep="24" />

    <

     

    defaultRate oid="E2B9D4FE-FD89-45FC-8E2D-BB70BA449B65" minStep="0" maxStep="24">

    <

     

    rateType oid="0370A010-C845-47B4-998F-C8F34C8D0D03" />

    Friday, March 12, 2010 4:08 AM
  • Still an issue?

    Thanks!


    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, April 25, 2013 5:25 AM
    Owner