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>
>
Showing posts with label xmllttaga. Show all posts
Showing posts with label xmllttaga. Show all posts
Monday, March 12, 2012
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:
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>
>
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/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:
>
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:
>
Subscribe to:
Posts (Atom)