Query to get XML in output (SQL Server 2000)

Hi all,
Can anyone help me with a query to get the following XMLs in result
First XML
<TagA id="NR_2">
<TagB id="2">Factory</TagB>
<TagC>WASHINGTON</TagC>
<TagD>999999999</TagD>
<TagE id="123456">Other</TagE>
<TagF>
<TagG>COMPANY</TagG>
<TagH>0123</TagH>
</TagF>
</TagA>
Second XML
<TagA id="NR_3">
<TagB id="2">Factory</TagB>
<TagC>GEORGIA</TagC>
<TagD>GA</TagD>
<TagE id="123456">Other</TagE>
<TagF/>
</TagA>Umar,
If you haven't already, you may want to see:
Using EXPLICIT Mode
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_4y91.asp
HTH
Jerry
"Umar" <Umar@.discussions.microsoft.com> wrote in message
news:23A25C27-983E-46B0-A12F-1C7EF28812D0@.microsoft.com...
> Hi all,
> Can anyone help me with a query to get the following XMLs in result
> First XML
> <TagA id="NR_2">
> <TagB id="2">Factory</TagB>
> <TagC>WASHINGTON</TagC>
> <TagD>999999999</TagD>
> <TagE id="123456">Other</TagE>
> <TagF>
> <TagG>COMPANY</TagG>
> <TagH>0123</TagH>
> </TagF>
> </TagA>
>
>
> Second XML
> <TagA id="NR_3">
> <TagB id="2">Factory</TagB>
> <TagC>GEORGIA</TagC>
> <TagD>GA</TagD>
> <TagE id="123456">Other</TagE>
> <TagF/>
> </TagA>
>|||Hi Jerry,
I have seen this.
If you note, the tags have attributes and values. Is there a way I can do it
one sql. I know this can be done if child tags have values only. e.g,
<TagA id="NR_2">
<TagC>WASHINGTON</TagC>
</TagA>
But note that my desired result requires attributes in child tags too. I
know this can be done by using UNION ALL, but is there a way I do it one
query?
"Jerry Spivey" wrote:
> Umar,
> If you haven't already, you may want to see:
> Using EXPLICIT Mode
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_4y91.asp
> HTH
> Jerry
> "Umar" <Umar@.discussions.microsoft.com> wrote in message
> news:23A25C27-983E-46B0-A12F-1C7EF28812D0@.microsoft.com...
> > Hi all,
> >
> > Can anyone help me with a query to get the following XMLs in result
> >
> > First XML
> >
> > <TagA id="NR_2">
> > <TagB id="2">Factory</TagB>
> > <TagC>WASHINGTON</TagC>
> > <TagD>999999999</TagD>
> > <TagE id="123456">Other</TagE>
> > <TagF>
> > <TagG>COMPANY</TagG>
> > <TagH>0123</TagH>
> > </TagF>
> > </TagA>
> >
> >
> >
> >
> > Second XML
> >
> > <TagA id="NR_3">
> > <TagB id="2">Factory</TagB>
> > <TagC>GEORGIA</TagC>
> > <TagD>GA</TagD>
> > <TagE id="123456">Other</TagE>
> > <TagF/>
> > </TagA>
> >
>
>|||You can make something an attribute or an element if you use XML EXPLICIT. I
would try to stay away from keywords like "id"m but for reference I included
you sample code below. Use the attribute name to make it an attribute, use
the keywork "element" to make it an element.
SELECT 1 as Tag,
NULL as Parent,
taga.id as [TagA!1!id],
NULL as [TagB!2!id],
NULL as [TagB!2!element]
FROM taga
UNION ALL
SELECT 2,
1,
taga.id,
tagb.id,
tagb.name
FROM taga INNER JOIN tagb ON taga.id = tagb.refid
ORDER BY [TagA!1!id], [TagB!2!id]
FOR XML EXPLICIT
HTH,
John Scragg
"Umar" wrote:
> Hi Jerry,
> I have seen this.
> If you note, the tags have attributes and values. Is there a way I can do it
> one sql. I know this can be done if child tags have values only. e.g,
> <TagA id="NR_2">
> <TagC>WASHINGTON</TagC>
> </TagA>
> But note that my desired result requires attributes in child tags too. I
> know this can be done by using UNION ALL, but is there a way I do it one
> query?
> "Jerry Spivey" wrote:
> > Umar,
> >
> > If you haven't already, you may want to see:
> >
> > Using EXPLICIT Mode
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_4y91.asp
> >
> > HTH
> >
> > Jerry
> > "Umar" <Umar@.discussions.microsoft.com> wrote in message
> > news:23A25C27-983E-46B0-A12F-1C7EF28812D0@.microsoft.com...
> > > Hi all,
> > >
> > > Can anyone help me with a query to get the following XMLs in result
> > >
> > > First XML
> > >
> > > <TagA id="NR_2">
> > > <TagB id="2">Factory</TagB>
> > > <TagC>WASHINGTON</TagC>
> > > <TagD>999999999</TagD>
> > > <TagE id="123456">Other</TagE>
> > > <TagF>
> > > <TagG>COMPANY</TagG>
> > > <TagH>0123</TagH>
> > > </TagF>
> > > </TagA>
> > >
> > >
> > >
> > >
> > > Second XML
> > >
> > > <TagA id="NR_3">
> > > <TagB id="2">Factory</TagB>
> > > <TagC>GEORGIA</TagC>
> > > <TagD>GA</TagD>
> > > <TagE id="123456">Other</TagE>
> > > <TagF/>
> > > </TagA>
> > >
> >
> >
> >