FOR XML PATH NULL Element

Hi there,
I'm using sp with FOR XML PATH('Employee'), ELEMENTS to return XML Data
from SQL Server 2005.
If row return null value return xml does not return element.
Can it be returned xml element even it contains null?
Ex
i'm getting this
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
</employee>
i want this:)
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
<image/>
</employee>
*** Sent via Developersdex http://www.examnotes.net ***Hello Zoka,
You could do something like this:
SELECT
..
e.image AS "image/node()"
,'' AS "image/node()" -- Same as above :-), now "image/node()" is never
NULL :-)
..
FROM ... AS e
FOR XML PATH('employee')
HTH
/ Tobias|||
Hi there,
I tried this functionality but does not solve the problem.
*** Sent via Developersdex http://www.examnotes.net ***|||
Sorry Tobias,
This solves my problem, thanks:))
I haven't drink coffe when i first try the script:)
Regards,
Zoka
*** Sent via Developersdex http://www.examnotes.net ***

FOR XML Explicit query

I need to output a query to xml with a Type Coded element, but am not sure how to format it in SQL. The result should look like this:

<Person>

<Name>Test</Name>

<Gender tc="1"/>

</Person>

Any help would be appreciated

Thanks

Stphane

Use this format:

SELECT 'Test' AS 'Person/Name', '1' AS 'Person/Gender/@.tc' FOR XML PATH(''), TYPE;

It will yield the following xml for you.

<Person><Name>Test</Name><Gender tc="1" /></Person>

(Note that "FOR XML PATH" is new in SQL Server 2005)

FOR XML Explicit query

I need to output a query to xml with a Type Coded element, but am not sure how to format it in SQL. The result should look like this:

<Person>

<Name>Test</Name>

<Gender tc="1"/>

</Person>

Any help would be appreciated

Thanks

Stphane

Use this format:

SELECT 'Test' AS 'Person/Name', '1' AS 'Person/Gender/@.tc' FOR XML PATH(''), TYPE;

It will yield the following xml for you.

<Person><Name>Test</Name><Gender tc="1" /></Person>

(Note that "FOR XML PATH" is new in SQL Server 2005)

FOR XML EXPLICIT and optional attributes

If attributes of an element are optional can XML Explicit be used to
only return such attrubutes if their value is not null. eg. to return
something like:
<Person Name = "Paul" BicycleBrand = "Raliegh">
<Person Name = "John" CarBrand = "Ford">
Rather than
<Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
<Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
?It is the default behaviour of FOR XML EXPLICIT to map null value to absent
attributes.
Best regards
Michael
<paul.l@.paloma.co.uk> wrote in message
news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
> If attributes of an element are optional can XML Explicit be used to
> only return such attrubutes if their value is not null. eg. to return
> something like:
> <Person Name = "Paul" BicycleBrand = "Raliegh">
> <Person Name = "John" CarBrand = "Ford">
> Rather than
> <Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
> <Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
> ?
>|||Thanks
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
to
return|||Thanks. Thought it might be simple.
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
to
return

FOR XML EXPLICIT and optional attributes

If attributes of an element are optional can XML Explicit be used to
only return such attrubutes if their value is not null. eg. to return
something like:
<Person Name = "Paul" BicycleBrand = "Raliegh">
<Person Name = "John" CarBrand = "Ford">
Rather than
<Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
<Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
?
It is the default behaviour of FOR XML EXPLICIT to map null value to absent
attributes.
Best regards
Michael
<paul.l@.paloma.co.uk> wrote in message
news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
> If attributes of an element are optional can XML Explicit be used to
> only return such attrubutes if their value is not null. eg. to return
> something like:
> <Person Name = "Paul" BicycleBrand = "Raliegh">
> <Person Name = "John" CarBrand = "Ford">
> Rather than
> <Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
> <Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
> ?
>
|||Thanks
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent[vbcol=seagreen]
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
to[vbcol=seagreen]
return[vbcol=seagreen]
|||Thanks. Thought it might be simple.
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent[vbcol=seagreen]
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
to[vbcol=seagreen]
return[vbcol=seagreen]

FOR XML clause and <root> element

Hi,
When I run the following query in Query Analyzer, it works fine, but I
cannot add <ROOT> ... </ROOT> element to result:
SELECT * FROM
(
select top 10
CompanyName,
SUM((CONVERT(money,("Order
Details".UnitPrice*Quantity*(1-Discount)/100))*100)) AS Amount
, count(*) OrderItemCount
from
customers
inner join orders on orders.CustomerID = customers.CustomerID
inner join [Order Details] on Orders.OrderID = [Order Details].OrderID
GROUP BY
CompanyName
) AS GoodCustomers
FOR XML AUTO ,ELEMENTS
Ho can add <root> element to the result?
Thank you,
AlanHi
You can do that externally such as http://sqlxml.org/faqs.aspx?faq=10
This may also help http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp
John
"A.M" wrote:

>
> Hi,
>
> When I run the following query in Query Analyzer, it works fine, but I
> cannot add <ROOT> ... </ROOT> element to result:
>
>
> SELECT * FROM
> (
> select top 10
> CompanyName,
> SUM((CONVERT(money,("Order
> Details".UnitPrice*Quantity*(1-Discount)/100))*100)) AS Amount
> , count(*) OrderItemCount
> from
> customers
> inner join orders on orders.CustomerID = customers.CustomerID
> inner join [Order Details] on Orders.OrderID = [Order Details].OrderID
> GROUP BY
> CompanyName
> ) AS GoodCustomers
> FOR XML AUTO ,ELEMENTS
>
>
>
>
>
> Ho can add <root> element to the result?
>
> Thank you,
> Alan
>
>|||Hi,
Additional to what John recommanded, there is a new property that you can
use to tell the OLEDB provider to add
the root tag for you.
command.Properties("Output Stream") = response
command.Properties("XML Root") = "root"
command.Execute , , 1024
Then the stream you are getting back will have "XML Root" as the document
root tag.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

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