Re: XML output

From: "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx>
Date: Tue, 11 Jul 2006 15:25:22 -0700

Your query is not very stable... You should make sure that you get the right
ordering of the rows by adding the ORDER BY columns, e,g,

SELECT 1 AS Tag,
NULL AS Parent,
1 AS [xml!1!dummy!hide],
NULL AS [SearchResult!2!dummy!hide],
NULL AS [Book!3!BookID],
NULL AS [Book!3!Title],
NULL AS [Book!3!Author],
NULL AS [Book!3!BookVisibility],
NULL AS [Book!3!NextAvailDate],
NULL AS [Book!3!CategoryName]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
1,
1,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL
UNION ALL

SELECT 3,2,1,1,BookID, Title, Author,
BookVisibility,
CASE WHEN ToBeReturnedOn < CAST(CONVERT(varchar(11),GETDATE(),113) as
smallDatetime ) OR ToBeReturnedOn IS NULL THEN
CONVERT(varchar(11),GETDATE(),113)
WHEN ToBeReturnedOn = CAST(CONVERT(varchar(11),GETDATE(),113) as
smallDatetime ) THEN CONVERT(varchar(11),GETDATE()+1,113)
ELSE CONVERT(varchar(11),ToBeReturnedOn ,113) END as NextAvailDate,
CategoryName
FROM dbo.Books B(NOLOCK)
INNER JOIN dbo.Category C(NOLOCK)
ON B.CategoryID = C.CategoryID
ORDER BY [xml!1!dummy!hide], [SearchResult!2!dummy!hide], [Book!3!BookID]
FOR XML EXPLICIT

Alternatively, you may want to look into FOR XML Path if you have SQL Server
2005 (here a simple example using the Customers table from Northwind):

select (select * from Customers

FOR XML PATH('Customer'), type, ROOT('SearchResult'))

FOR XML PATH('xml')

HTH

Michael

"aneeshattingal" <aneeshattingal@xxxxxxxxxxx> wrote in message
news:%23MXB8ltjGHA.1936@xxxxxxxxxxxxxxxxxxxxxxx

I got the output
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [xml!1!SearchResult],
NULL AS [SearchResult!2!Book],
NULL AS [Book!3!BookID],
NULL AS [Book!3!Title],
NULL AS [Book!3!Author],
NULL AS [Book!3!BookVisibility],
NULL AS [Book!3!NextAvailDate],
NULL AS [Book!3!CategoryName]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
NULL ,
NULL,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL
UNION ALL

SELECT 3,2,NULL,NULL,BookID, Title, Author,
BookVisibility,
CASE WHEN ToBeReturnedOn < CAST(CONVERT(varchar(11),GETDATE(),113) as
smallDatetime ) OR ToBeReturnedOn IS NULL THEN
CONVERT(varchar(11),GETDATE(),113)
WHEN ToBeReturnedOn = CAST(CONVERT(varchar(11),GETDATE(),113) as
smallDatetime ) THEN CONVERT(varchar(11),GETDATE()+1,113)
ELSE CONVERT(varchar(11),ToBeReturnedOn ,113) END as NextAvailDate,
CategoryName
FROM dbo.Books B(NOLOCK)
INNER JOIN dbo.Category C(NOLOCK)
ON B.CategoryID = C.CategoryID
FOR XML EXPLICIT

"aneeshattingal" <aneeshattingal@xxxxxxxxxxx> wrote in message
news:OzhmQvrjGHA.3408@xxxxxxxxxxxxxxxxxxxxxxx

Hi,
I need some help in order to generate an XML out put.
The following Query generates the xml in the following manner ,
But What I need is something like the one i shown as desired output

Query

SELECT 1 AS Tag,
NULL AS Parent,
BookID AS [Book!1!BookID],
Title AS [Book!1!Title],
Author AS [Book!1!Author],
BookVisibility AS [Book!1!BookVisibility],
NULL AS [Book!1!NextAvailDate],
CategoryName AS [Book!1!CategoryName]
FROM dbo.Books B(NOLOCK)
INNER JOIN dbo.Category C(NOLOCK)
ON B.CategoryID = C.CategoryID

UNION ALL

SELECT 1,NULL,BookID, Title, Author,
BookVisibility,
CASE WHEN ToBeReturnedOn < CAST(CONVERT(varchar(11),GETDATE(),113) as
smallDatetime ) OR ToBeReturnedOn IS NULL THEN
CONVERT(varchar(11),GETDATE(),113)
WHEN ToBeReturnedOn = CAST(CONVERT(varchar(11),GETDATE(),113) as
smallDatetime ) THEN CONVERT(varchar(11),GETDATE()+1,113)
ELSE CONVERT(varchar(11),ToBeReturnedOn ,113) END as

NextAvailDate,

CategoryName
FROM dbo.Books B(NOLOCK)
INNER JOIN dbo.Category C(NOLOCK)
ON B.CategoryID = C.CategoryID
FOR XML EXPLICIT

Current Output
--------------
<Book BookID="4" Title="Software Engineering" Author="Roger.S. Pressman "
BookVisibility="All" CategoryName="Database" />
<Book BookID="5" Title="Managing Global Software Projects "
Author="Ramesh
Gopalswamy " BookVisibility="All" CategoryName="Database" />

Desired Output
--------------
<xml>
<SearchResults>
<Book BookID="4" Title="Software Engineering" Author="Roger.S.

Pressman

" BookVisibility="All" CategoryName="Database" />
<Book BookID="5" Title="Managing Global Software Projects "
Author="Ramesh Gopalswamy " BookVisibility="All" CategoryName="Database"

/>

</SearchResults>
</xml>

Prev by Date: Re: problem with an xpath parameter to StoredProc
Next by Date: Re: xml datatype, UTF and BMP
Previous by thread: Re: problem with an xpath parameter to StoredProc
Next by thread: Re: xml datatype, UTF and BMP
Index(es):
- Date
- Thread

Relevant Pages

Re: XML output
... SELECT 1 AS Tag, ... NULL AS Parent, ... smallDatetime) OR ToBeReturnedOn IS NULL THEN ... I need some help in order to generate an XML out put. ...
(microsoft.public.sqlserver.xml)
XML output
... I need some help in order to generate an XML out put. ... INNER JOIN dbo.Category C ... smallDatetime) OR ToBeReturnedOn IS NULL THEN ...
(microsoft.public.sqlserver.xml)