Friday, March 30, 2012

Help on XML Explicit

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.FecEst AS [e!3!fe!xml]
FROM tDE_PlanillaxEstado_Observacion DE_PlEstObs INNER JOIN
tDE_Planilla DE_Pl ON DE_PlEstObs.IdPla =
DE_Pl.IdPla INNER JOIN
tDE_Estado DE_Est ON DE_PlEstObs.CodEst =
DE_Est.CodEst
WHERE DE_Pl.IdCat = @.IdCat
AND DE_PlEstObs.FecEst = (SELECT MIN(DE_PlEstObs2.FecEst) FROM
tDE_PlanillaxEstado_Observacion DE_PlEstObs2 WHERE
DE_PlEstObs2.IdPla=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.Please post DDL and some sample data. Can't guess your data.
ML

No comments:

Post a Comment