Query to get XML in output (SQL Server 2000)

Hi all,
Can anyone help me with a SQL query to get the following XMLs in result.
This is for SQL Server 2000
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>
Look at the FOR XML EXPLICIT functionality.
If you could provide us with some example table, we could probably help you
with the query.
Best regards
Michael
"Umar" <Umar@.discussions.microsoft.com> wrote in message
news:1F4BC4B9-186A-46D8-BFB0-BA0BD657B184@.microsoft.com...
> Hi all,
> Can anyone help me with a SQL query to get the following XMLs in result.
> This is for SQL Server 2000
> 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>
>

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/de...enxml_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/de...enxml_4y91.asp
> HTH
> Jerry
> "Umar" <Umar@.discussions.microsoft.com> wrote in message
> news:23A25C27-983E-46B0-A12F-1C7EF28812D0@.microsoft.com...
>
>
|||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:
[vbcol=seagreen]
> 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:

Query to get XML in output (SQL Server 2000)

Hi all,
Can anyone help me with a SQL query to get the following XMLs in result.
This is for SQL Server 2000
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>Look at the FOR XML EXPLICIT functionality.
If you could provide us with some example table, we could probably help you
with the query.
Best regards
Michael
"Umar" <Umar@.discussions.microsoft.com> wrote in message
news:1F4BC4B9-186A-46D8-BFB0-BA0BD657B184@.microsoft.com...
> Hi all,
> Can anyone help me with a SQL query to get the following XMLs in result.
> This is for SQL Server 2000
> 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>
>

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

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/d...r />
_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/d.../>
ml_4y91.asp
> HTH
> Jerry
> "Umar" <Umar@.discussions.microsoft.com> wrote in message
> news:23A25C27-983E-46B0-A12F-1C7EF28812D0@.microsoft.com...
>
>|||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:
[vbcol=seagreen]
> 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:
>

Query to be resolved

Hi,
if anybody can reslove this sql query :
Fld1 Fld2
=== =====
1 100
1 200
2 400
2 401
2 402
=================
I tried but cud not suceed to bring this output :
fld1 fld2
===============
1 100
2 400
I think i could put forward my question clearly...pls try if anybody can
resolve it.
Thanks in advance.......
GDTry,
select fld1, min(fld2) as min_fld2
from t1
group by gld1
go
AMB
"Dubey Gopal" wrote:

> Hi,
> if anybody can reslove this sql query :
> Fld1 Fld2
> === =====
> 1 100
> 1 200
> 2 400
> 2 401
> 2 402
> =================
> I tried but cud not suceed to bring this output :
> fld1 fld2
> ===============
> 1 100
> 2 400
> I think i could put forward my question clearly...pls try if anybody can
> resolve it.
> Thanks in advance.......
> GD
>|||Thanks AMB...It is working fine !!
GD
"Alejandro Mesa" wrote:
> Try,
> select fld1, min(fld2) as min_fld2
> from t1
> group by gld1
> go
>
> AMB
> "Dubey Gopal" wrote:
>