Help on XML Explicit

[maguca]

Hello, I am starting to work with XML Explicit. I am having problems
with the tags and the level they generate in.

Can anyone please help me, for I have looked aroung and it seems that I
am doing everything fine!!!

I am attaching below an example of the query and the XML it generates.
=============== ============

Declare @IdCat int
set @IdCat = 62

SELECT
1 AS TAG,
NULL AS PARENT,

'' AS [p!1],
NULL AS [pc!2],

NULL AS [pc!2!npc!xml],
NULL AS [pc!2!idp!xml],
NULL AS [pc!2!nc!xml],
NULL AS [pc!2!m!xml],

NULL AS [e!3!xml],

NULL AS [e!3!ce!xml],
NULL AS [e!3!de!xml],
NULL AS [e!3!fe!xml]

FROM tDE_Cataporte DE_Cat
WHERE DE_Cat.IdCat = @IdCat
UNION ALL
SELECT
2 AS TAG,
1 AS PARENT,

NULL AS [p!1],
'' AS [pc!2],

DE_Pl.NumPlaCli AS [pc!2!npc!xml],
DE_Pl.IdPla AS [pc!2!idp!xml],
DE_Pl.NumCta AS [pc!2!nc!xml],
DE_Pl.Monto AS [pc!2!m!xml],

NULL AS [e!3!xml],

NULL AS [e!3!ce!xml],
NULL AS [e!3!de!xml],
NULL AS [e!3!fe!xml]

FROM tDE_Planilla DE_Pl
WHERE DE_Pl.IdCat = @IdCat
UNION ALL
SELECT
3 AS TAG,
2 AS PARENT,

NULL AS [p!1],
NULL AS [pc!2],

NULL AS [pc!2!npc!xml],
NULL AS [pc!2!idp!xml],
NULL AS [pc!2!nc!xml],
NULL AS [pc!2!m!xml],

'' AS [e!3!xml],

DE_Est.CodEst AS [e!3!ce!xml],
DE_Est.DesEst AS [e!3!de!xml],
DE_PlEstObs.Fec Est AS [e!3!fe!xml]

FROM tDE_PlanillaxEs tado_Observacio n DE_PlEstObs INNER JOIN
tDE_Planilla DE_Pl ON DE_PlEstObs.IdP la =
DE_Pl.IdPla INNER JOIN
tDE_Estado DE_Est ON DE_PlEstObs.Cod Est =
DE_Est.CodEst
WHERE DE_Pl.IdCat = @IdCat
AND DE_PlEstObs.Fec Est = (SELECT MIN(DE_PlEstObs 2.FecEst) FROM
tDE_PlanillaxEs tado_Observacio n DE_PlEstObs2 WHERE
DE_PlEstObs2.Id Pla=DE_Pl.IdPla )

FOR XML Explicit

<p>
<pc>
<npc>888</npc>
<idp>58</idp>
<nc>9939</nc>
<m>20000</m>
</pc>
<pc>
<npc>555</npc>
<idp>60</idp>
<nc>00018</nc>
<m>131150</m>
</pc>
<pc>
<npc>753</npc>
<idp>61</idp>
<nc>20018</nc>
<m>40300</m>
<e xml="">
<ce>0</ce>
<de>Borrador</de>
<fe>2005-07-07T16:06:04.130 </fe>
</e>
<e xml="">
<ce>0</ce>
<de>Borrador</de>
<fe>2005-07-08T10:40:12.390 </fe>
</e>
<e xml="">
<ce>0</ce>
<de>Borrador</de>
<fe>2005-07-08T11:39:32.830 </fe>
</e>
</pc>
</p>
=============== =============== =============== =============

As you can see the tag 3 (<e xml="">) is only generated at the end when
there should be one <e> for each <pc>.

Thank you in advance for your help.

[n_o_s_p_a__m]

I recommend that you use an ORDER BY clause.They are important in
explicit queries.

Try something like (untested):

ORDER BY [p!1], [pc!2], [e!3]

You may have to add the subelement or attribute names to these.

Another good trick I use is to comment out the FOR XML EXPLICIT bit and
look at the edge table that is being produced. That will give you some
insight.

p.s.: You might get better responses if you post to
microsoft.publi c.sqlserver.pro gramming.