FOR XML? UPDATED ANSWER (WAS: Newbie question...)

Apologies on the last post. I did not see FOR XML and was thinking of other
SQL capabilities.
FOR XML creates a valid XML snippet, without root tags. This gives you a
couple of choices.
1. Create templates that supply root tags
2. Supply root tags in your program
For XML input into SQL Server, you WILL end up with a blow up, as mentioned
in the prior response.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"VK" wrote:

> Hi,
> Will SQL XML (when we use FOR XML) perform check to make sure that XML
> document is in proper form or do we have to use a third party s/w like
> Altova to check for correctly created XML document?
> Many thanks for your time.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
Let me clarify this a bit further.
FOR XML in most cases (except for invalid characters and when using the
xmltext directive in EXPLICIT mode) will generate well-formed fragments. You
can set a root property on your provider (ADO, ADO.net, or the HTTP ISAPI)
to have it wrapped.
When you use sp_xml_preparedocument to parse incoming XML data, you will get
an error message if the parsing fails. So it does not quite explode :-).
Best regards
Michael
"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@.comcast.netNoSpamM> wrote
in message news:B30C9949-8C15-46CE-826B-4961014B9B19@.microsoft.com...[vbcol=seagreen]
> Apologies on the last post. I did not see FOR XML and was thinking of
> other
> SQL capabilities.
> FOR XML creates a valid XML snippet, without root tags. This gives you a
> couple of choices.
> 1. Create templates that supply root tags
> 2. Supply root tags in your program
> For XML input into SQL Server, you WILL end up with a blow up, as
> mentioned
> in the prior response.
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> ***************************
> Think Outside the Box!
> ***************************
> "VK" wrote:

FOR XML? UPDATED ANSWER (WAS: Newbie question...)

Apologies on the last post. I did not see FOR XML and was thinking of other
SQL capabilities.
FOR XML creates a valid XML snippet, without root tags. This gives you a
couple of choices.
1. Create templates that supply root tags
2. Supply root tags in your program
For XML input into SQL Server, you WILL end up with a blow up, as mentioned
in the prior response.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"VK" wrote:

> Hi,
> Will SQL XML (when we use FOR XML) perform check to make sure that XML
> document is in proper form or do we have to use a third party s/w like
> Altova to check for correctly created XML document?
> Many thanks for your time.
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>Let me clarify this a bit further.
FOR XML in most cases (except for invalid characters and when using the
xmltext directive in EXPLICIT mode) will generate well-formed fragments. You
can set a root property on your provider (ADO, ADO.net, or the HTTP ISAPI)
to have it wrapped.
When you use sp_xml_preparedocument to parse incoming XML data, you will get
an error message if the parsing fails. So it does not quite explode :-).
Best regards
Michael
"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@.comcast.netNoSpamM> wrote
in message news:B30C9949-8C15-46CE-826B-4961014B9B19@.microsoft.com...
> Apologies on the last post. I did not see FOR XML and was thinking of
> other
> SQL capabilities.
> FOR XML creates a valid XML snippet, without root tags. This gives you a
> couple of choices.
> 1. Create templates that supply root tags
> 2. Supply root tags in your program
> For XML input into SQL Server, you WILL end up with a blow up, as
> mentioned
> in the prior response.
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> ***************************
> Think Outside the Box!
> ***************************
> "VK" wrote:
>

For XML Path

im using the ROOT directive in association with FOR XML PATH to return results. When the query returns no records then I get no root node either (which makes the XML invalid). Is there an attribute that specifies that the root node should always be returned (even when empty)?

i.e. <ROOT />

thx

Not that I know of. Since no rows were returned, not data would be returned at all. You could do something like this:

select cast(
'<root>' +

coalesce((select *
from sys.objects
where 1=2 --change to 1=1 to get rows
for xml path),'')

+ '</root>' as xml)

This does seem to work, though not 100% sure if there will be much of a performance hit.

|||

thanks for the tip :)

im am confused becuase if i say that i want a resultset typed as xml, then i would expect (for the xml to be valid) that it has a root node regardless..... what concept am i missing if this is not the case?

|||I think the fact is, it isn't invalid XML, it is nothing. So if you return no data, then no XML is created.|||

i agree

BUT :)

that does mean that anything that uses the resultset requires a condition to check if it is Null and either 1)do nothing, or 2) subsitute it for what would be valid xml i.e. an empty parent node eg <Root />

i think a lot of applications would require number 2, and therefore think the XML functionality of sql2005 should have this built in.

|||

And I don't disagree with you, though you can use the cast and concatenation thing I posted earlier as a workaround.

If noone posts that I was wrong, consider posting your suggestion here: https://connect.microsoft.com/SQLServer/Feedback and then post in this thread that you have, and I will vote for it.

|||

how about this:-

CREATE PROCEDURE [dbo].[up_DoStuff]
(
-- params
)
AS
SET NOCOUNT ON;

DECLARE @.pXML XML
SET @.pXML = (
SELECT
...
FROM
...
WHERE
...
FOR
XML Path('Test'),
ELEMENTS,
ROOT('Tests'),
TYPE
)

SELECT ISNULL(@.pXML, '<Tests/>')

For XML Path

im using the ROOT directive in association with FOR XML PATH to return results. When the query returns no records then I get no root node either (which makes the XML invalid). Is there an attribute that specifies that the root node should always be returned (even when empty)?

i.e. <ROOT />

thx

Not that I know of. Since no rows were returned, not data would be returned at all. You could do something like this:

select cast(
'<root>' +

coalesce((select *
from sys.objects
where 1=2 --change to 1=1 to get rows
for xml path),'')

+ '</root>' as xml)

This does seem to work, though not 100% sure if there will be much of a performance hit.

|||

thanks for the tip :)

im am confused becuase if i say that i want a resultset typed as xml, then i would expect (for the xml to be valid) that it has a root node regardless..... what concept am i missing if this is not the case?

|||I think the fact is, it isn't invalid XML, it is nothing. So if you return no data, then no XML is created.|||

i agree

BUT :)

that does mean that anything that uses the resultset requires a condition to check if it is Null and either 1)do nothing, or 2) subsitute it for what would be valid xml i.e. an empty parent node eg <Root />

i think a lot of applications would require number 2, and therefore think the XML functionality of sql2005 should have this built in.

|||

And I don't disagree with you, though you can use the cast and concatenation thing I posted earlier as a workaround.

If noone posts that I was wrong, consider posting your suggestion here: https://connect.microsoft.com/SQLServer/Feedback and then post in this thread that you have, and I will vote for it.

|||

how about this:-

CREATE PROCEDURE [dbo].[up_DoStuff]
(
-- params
)
AS
SET NOCOUNT ON;

DECLARE @.pXML XML
SET @.pXML = (
SELECT
...
FROM
...
WHERE
...
FOR
XML Path('Test'),
ELEMENTS,
ROOT('Tests'),
TYPE
)

SELECT ISNULL(@.pXML, '<Tests/>')

For Xml Auto Help

In sql 2005 if I have a query along the lines of:
select * from myTable
for xml auto, elements xsinil, root('myRoot')
I will get back one row, one column, filled with a nice xml string.
The problem I'm having is figuring out how to access that data, such that I
can put it into a local var. What I want to do looks something like this:
declare @.foo varchar(max)
select @.foo = * from myTable
for xml auto, elements xsinil, root('myRoot')
select @.foo
This brings about an error of course, even though the final result is just
one row and column of data.
Does anybody know how I can get the results of a query using "for xml auto"
into a local variable?
Thanks,
KevinJust use an xml type variable e.g.
declare @.x xml
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'))
select @.x as 'XML Result'
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>|||Just to add a tiny bit:
The TYPE directive mean that the result from the query is of the datatype XM
L instead of a string.
It doesn't make any difference in Jasper's example, as there would be an imp
licit datatype
conversation from string to xml anyhow, but it might be useful in other case
s. The TYPE directive is
obviously new for 2005 (as the xml datatype is).
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'), type)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u9$Y3ODDGHA.2644@.TK2MSFTNGP09.phx.gbl...
> Just use an xml type variable e.g.
> declare @.x xml
> set @.x = (select [name]
> from sys.databases as [database]
> for xml auto,root('databases'))
> select @.x as 'XML Result'
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
> news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Jasper and Tibor, that's just what I needed.
Kevin
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>

FOR XML - Root Element

Hi,
Does anybody know how to add a root element to the output of a stored
procedure that returns data, using FOR XML statement. I am using SQL server
2000.
Thanks
Your provider should have the ability to set the root property. For example,
the ADO ICommandStream has a property that will add a root element. Ditto
for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks
|||Michael,
Thanks for your response.
I can't find any property that will allow me to specify a root element. I
am using a XmlReader and sqlCommand to serialize an object based on the
results of a stored procedure which returns XML.
Due to our environment it is not possible to use a SqlXMLcommand object.
Thanks for any further help.
Alex
"Michael Rys [MSFT]" wrote:

> Your provider should have the ability to set the root property. For example,
> the ADO ICommandStream has a property that will add a root element. Ditto
> for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
> Best regards
> Michael
> "Redowl" <Redowl@.discussions.microsoft.com> wrote in message
> news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
>
>
|||I think you have to use the SQLXML interfaces to add the root property.
In SQL Server 2000, you can also add a select N'<root>' before and select
N'</root>' after the stored proc invocation, IF you use the stream
interface.
Note that this is however not guaranteed to work well with the XML datatype
in 2005.
Also, in SQL Server 2005, you will be able to specify the root in your FOR
XML clause using a new ROOT directive...
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:0DACE436-377C-489B-A378-166ADEB6F0D5@.microsoft.com...[vbcol=seagreen]
> Michael,
> Thanks for your response.
> I can't find any property that will allow me to specify a root element. I
> am using a XmlReader and sqlCommand to serialize an object based on the
> results of a stored procedure which returns XML.
> Due to our environment it is not possible to use a SqlXMLcommand object.
> Thanks for any further help.
>
> Alex
> "Michael Rys [MSFT]" wrote:
|||In case you are still contemplating the answer to this in SQL Server 2000,
here is a great white paper from Dan Sullivan on how to achieve this:
http://www.sqlservicebroker.com/samp...medxmlauto.zip
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks

FOR XML - Root Element

Hi,
Does anybody know how to add a root element to the output of a stored
procedure that returns data, using FOR XML statement. I am using SQL server
2000.
ThanksYour provider should have the ability to set the root property. For example,
the ADO ICommandStream has a property that will add a root element. Ditto
for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks|||Michael,
Thanks for your response.
I can't find any property that will allow me to specify a root element. I
am using a XmlReader and sqlCommand to serialize an object based on the
results of a stored procedure which returns XML.
Due to our environment it is not possible to use a SqlXMLcommand object.
Thanks for any further help.
Alex
"Michael Rys [MSFT]" wrote:

> Your provider should have the ability to set the root property. For exampl
e,
> the ADO ICommandStream has a property that will add a root element. Ditto
> for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
> Best regards
> Michael
> "Redowl" <Redowl@.discussions.microsoft.com> wrote in message
> news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
>
>|||I think you have to use the SQLXML interfaces to add the root property.
In SQL Server 2000, you can also add a select N'<root>' before and select
N'</root>' after the stored proc invocation, IF you use the stream
interface.
Note that this is however not guaranteed to work well with the XML datatype
in 2005.
Also, in SQL Server 2005, you will be able to specify the root in your FOR
XML clause using a new ROOT directive...
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:0DACE436-377C-489B-A378-166ADEB6F0D5@.microsoft.com...
> Michael,
> Thanks for your response.
> I can't find any property that will allow me to specify a root element. I
> am using a XmlReader and sqlCommand to serialize an object based on the
> results of a stored procedure which returns XML.
> Due to our environment it is not possible to use a SqlXMLcommand object.
> Thanks for any further help.
>
> Alex
> "Michael Rys [MSFT]" wrote:
>|||In case you are still contemplating the answer to this in SQL Server 2000,
here is a great white paper from Dan Sullivan on how to achieve this:
http://www.sqlservicebroker.com/sam...rmedxmlauto.zip
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks