For XML Explicit Structure

Hello everyone,
I'm trying to create an XML file using FOR XML EXPLICIT. However, I
need part of my result to look like this:
<Serial>
<SerialNuber>123456</SerialNumber>
<SerialNuber>123457</SerialNumber>
<SerialNuber>123458</SerialNumber>
</Serial>
What is happening is that I am only able to get repeating attributes
to appear as follows:
<Serial>
<SerialNumber>123456</SerialNumber>
</Serial>
<Serial>
<SerialNumber>123457</SerialNumber>
</Serial>
<Serial>
<SerialNumber>123458</SerialNumber>
</Serial>
Does anyone know what I may be doing wrong?
Thanks!!
Rey
Bear in mind that FOR XML EXPLICIT only returns an XML fragment - not a
well-formed document. So your query could return the <SerialNumber>
elements, but your client application would have to add the <Serial> root
tag.
The query should look something like
SELECT 1 AS Tag,
NULL As Parent,
Serialno As [SerialNumber!1]
FROM myTable
FOR XML EXPLICIT
Adding the root tag depends on the client-side application used to execute
the query. The SQLXML OLEDB provider and the SQLXML Managed classes have a
property on the Command object to do that.
NB: FOR XML in SQL Server 2005 includes a ROOT directive to add a root tag
but I'm afraid in SQL Server 2000 you can only get a fragment.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Rey" <rdiaz@.hotmail.com> wrote in message
news:a696a6c7.0408240723.1ae34deb@.posting.google.c om...
Hello everyone,
I'm trying to create an XML file using FOR XML EXPLICIT. However, I
need part of my result to look like this:
<Serial>
<SerialNuber>123456</SerialNumber>
<SerialNuber>123457</SerialNumber>
<SerialNuber>123458</SerialNumber>
</Serial>
What is happening is that I am only able to get repeating attributes
to appear as follows:
<Serial>
<SerialNumber>123456</SerialNumber>
</Serial>
<Serial>
<SerialNumber>123457</SerialNumber>
</Serial>
<Serial>
<SerialNumber>123458</SerialNumber>
</Serial>
Does anyone know what I may be doing wrong?
Thanks!!
Rey
|||You could add a second select clause in the explicit mode to add a root
clause (although that is not as performant as using the provider property).
See the FOR XML in SQL Server 2005 whitepaper on MSDN for an example of such
an explicit mode query
(http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp).
HTH
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:%23iJQXKgiEHA.4056@.TK2MSFTNGP09.phx.gbl...
> Bear in mind that FOR XML EXPLICIT only returns an XML fragment - not a
> well-formed document. So your query could return the <SerialNumber>
> elements, but your client application would have to add the <Serial> root
> tag.
> The query should look something like
> SELECT 1 AS Tag,
> NULL As Parent,
> Serialno As [SerialNumber!1]
> FROM myTable
> FOR XML EXPLICIT
> Adding the root tag depends on the client-side application used to execute
> the query. The SQLXML OLEDB provider and the SQLXML Managed classes have a
> property on the Command object to do that.
> NB: FOR XML in SQL Server 2005 includes a ROOT directive to add a root tag
> but I'm afraid in SQL Server 2000 you can only get a fragment.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Rey" <rdiaz@.hotmail.com> wrote in message
> news:a696a6c7.0408240723.1ae34deb@.posting.google.c om...
> Hello everyone,
> I'm trying to create an XML file using FOR XML EXPLICIT. However, I
> need part of my result to look like this:
> <Serial>
> <SerialNuber>123456</SerialNumber>
> <SerialNuber>123457</SerialNumber>
> <SerialNuber>123458</SerialNumber>
> </Serial>
> What is happening is that I am only able to get repeating attributes
> to appear as follows:
> <Serial>
> <SerialNumber>123456</SerialNumber>
> </Serial>
> <Serial>
> <SerialNumber>123457</SerialNumber>
> </Serial>
> <Serial>
> <SerialNumber>123458</SerialNumber>
> </Serial>
> Does anyone know what I may be doing wrong?
> Thanks!!
> Rey
>
|||Michael is (as always) right on the money! You could write the query like
this:
SELECT 1 AS Tag,
NULL As Parent,
NULL As [Serial!1!dummyattribute],
NULL AS [SerialNumber!2]
UNION ALL
SELECT 2,
1,
NULL,
SerialNo
FROM myTable
ORDER BY Tag
FOR XML EXPLICIT
This would generate the root tag for you.
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:euFZISgiEHA.2704@.TK2MSFTNGP10.phx.gbl...
You could add a second select clause in the explicit mode to add a root
clause (although that is not as performant as using the provider property).
See the FOR XML in SQL Server 2005 whitepaper on MSDN for an example of such
an explicit mode query
(http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp).
HTH
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:%23iJQXKgiEHA.4056@.TK2MSFTNGP09.phx.gbl...
> Bear in mind that FOR XML EXPLICIT only returns an XML fragment - not a
> well-formed document. So your query could return the <SerialNumber>
> elements, but your client application would have to add the <Serial> root
> tag.
> The query should look something like
> SELECT 1 AS Tag,
> NULL As Parent,
> Serialno As [SerialNumber!1]
> FROM myTable
> FOR XML EXPLICIT
> Adding the root tag depends on the client-side application used to execute
> the query. The SQLXML OLEDB provider and the SQLXML Managed classes have a
> property on the Command object to do that.
> NB: FOR XML in SQL Server 2005 includes a ROOT directive to add a root tag
> but I'm afraid in SQL Server 2000 you can only get a fragment.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Rey" <rdiaz@.hotmail.com> wrote in message
> news:a696a6c7.0408240723.1ae34deb@.posting.google.c om...
> Hello everyone,
> I'm trying to create an XML file using FOR XML EXPLICIT. However, I
> need part of my result to look like this:
> <Serial>
> <SerialNuber>123456</SerialNumber>
> <SerialNuber>123457</SerialNumber>
> <SerialNuber>123458</SerialNumber>
> </Serial>
> What is happening is that I am only able to get repeating attributes
> to appear as follows:
> <Serial>
> <SerialNumber>123456</SerialNumber>
> </Serial>
> <Serial>
> <SerialNumber>123457</SerialNumber>
> </Serial>
> <Serial>
> <SerialNumber>123458</SerialNumber>
> </Serial>
> Does anyone know what I may be doing wrong?
> Thanks!!
> Rey
>