XML help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

XML help

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-15 : 03:06:33

Hi all,

i have stored procedure which will give ouput like this...
<ArticleAuthor>
<FIS_Sub_Author Sub_Author_ID="5339">
<FIS_Sub_Author_Affliation Sub_Aff_ID="4877" Row="3" IsDeleted="True" AffSeq_No="1" />
<FIS_Sub_Author_Affliation Sub_Aff_ID="4875" Row="1" IsDeleted="True" AffSeq_No="2" />
<FIS_Sub_Author_Affliation Sub_Aff_ID="4876" Row="2" IsDeleted="True" AffSeq_No="3" />
</FIS_Sub_Author>
<FIS_Sub_Author Sub_Author_ID="5341">
<FIS_Sub_Author_Affliation Sub_Aff_ID="4879" Row="2" IsDeleted="True" AffSeq_No="1" />
<FIS_Sub_Author_Affliation Sub_Aff_ID="4876" Row="1" IsDeleted="True" AffSeq_No="2" />
<FIS_Sub_Author_Affliation Sub_Aff_ID="4880" Row="3" IsDeleted="True" AffSeq_No="3" />
</FIS_Sub_Author>
</ArticleAuthor>

in this case for Sub_Aff_ID="4876" have two diff row values in two nodes... i want to get the output in which it has to sort according to Sub_Aff_ID if two same Sub_Aff_ID="4876" are there in two nodes it should same values how i can do that????

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-15 : 03:08:01

can i see your currently used query please?

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-15 : 03:09:39

IF(@Sub_ID <> 0 AND @Sub_Author_ID <> 0)
BEGIN
SELECT
FIS_Sub_Author.Sub_Author_ID,

(SELECT FIS_Sub_Author_Affliation.Sub_Aff_ID,Row_number()OVER(ORDER BY FIS_Sub_Author_Affliation.Sub_Aff_ID ) AS 'Row',
CASE
WHEN FIS_Sub_Affliation.IsDeleted = 0
THEN 'True'
WHEN FIS_Sub_Affliation.IsDeleted = 1
THEN 'False'
End AS IsDeleted,
ISNULL(FIS_Sub_Affliation.AffSeq_No ,'')AS AffSeq_No

FROM FIS_Sub_Affliation WITH(NOLOCK)
INNER JOIN FIS_Sub_Author_Affliation WITH(NOLOCK) ON FIS_Sub_Author_Affliation.Sub_Aff_ID = FIS_Sub_Affliation.Sub_Aff_ID
LEFT JOIN FIS_User_Title WITH(NOLOCK) ON FIS_Sub_Author.Title = FIS_User_Title.Title_ID
LEFT JOIN FIS_Org_Category WITH(NOLOCK) ON FIS_Sub_Author_Affliation.Org_cat_ID = FIS_Org_Category.Org_Cat_ID
LEFT JOIN FIS_Org_Type WITH(NOLOCK) ON FIS_Sub_Author_Affliation.Org_Type_ID = FIS_Org_Type.Org_Type_ID
INNER JOIN FIS_Country WITH(NOLOCK) ON FIS_Sub_Author_Affliation.Country = FIS_Country.Country_ID
WHERE
FIS_Sub_Author.Sub_Author_ID= FIS_Sub_Affliation.Sub_Author_ID
AND
FIS_Sub_Author.Sub_ID = @Sub_ID AND FIS_Sub_Affliation.IsDeleted =0
FOR XML AUTO,TYPE)
FROM
FIS_Sub_Author WITH(NOLOCK)
LEFT JOIN FIS_User_Title WITH(NOLOCK) ON FIS_Sub_Author.Title = FIS_User_Title.Title_ID
WHERE
FIS_Sub_Author.Sub_ID = @Sub_ID
AND
FIS_Sub_Author.Sub_Author_ID = @Sub_Author_ID AND FIS_Sub_Author.status = 0
ORDER BY
FIS_Sub_Author.Seq_No
FOR XML AUTO,ROOT('ArticleAuthor')

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-15 : 03:43:08

ok so you want both nodes with value 4876 have same values for other fields?

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-15 : 03:46:58

which ever same fileds coming in xml should show same row number...

Regards,
Divya

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-15 : 03:55:59

i want to sort same attributes in different elements????

Regards,
Divya

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-15 : 04:25:23

is there any alternative so that i will get the desired output.....

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-15 : 04:37:44

for getting same value for all nodes with a particular value of Sub_Aff_ID use the below for getting row_number

dense_rank()OVER(ORDER BY FIS_Sub_Author_Affliation.Sub_Aff_ID )

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-15 : 06:34:48

i got the out put like this

<Event>
<FIS_EventType Event_Type="Conference">
<FIS_Sub_Article Article_Title="Conference Event By Aneesh" Created_Date="2010-01-15T14:36:16.043">
<FIS_Sub_Author First_Name="Aneesh" Last_Name="Salim" />
</FIS_Sub_Article>
<FIS_Sub_Article Article_Title="Conference Event By Aneesh" Created_Date="2010-01-15T14:36:16.043">
<FIS_Sub_Author First_Name="Kripa" Last_Name="V" />
</FIS_Sub_Article>
<FIS_Sub_Article Article_Title="Conference Event By Aneesh" Created_Date="2010-01-15T14:36:16.043">
<FIS_Sub_Author First_Name="Sajid" Last_Name="PC" />
</FIS_Sub_Article>
<FIS_Sub_Article Article_Title="Conference Event Article Title" Created_Date="2010-01-15T15:19:08.053">
<FIS_Sub_Author First_Name="Aneesh" Last_Name="Salim" />
</FIS_Sub_Article>
<FIS_Sub_Article Article_Title="Conference Event Article Title" Created_Date="2010-01-15T15:19:08.053">
<FIS_Sub_Author First_Name="Kripa" Last_Name="V" />
</FIS_Sub_Article>
<FIS_Sub_Article Article_Title="Conference Event Article Title" Created_Date="2010-01-15T15:19:08.053">
<FIS_Sub_Author First_Name="Sajid" Last_Name="PC" />
</FIS_Sub_Article>
<FIS_Sub_Article Article_Title="Testing Abstracts" Created_Date="2010-01-15T15:31:54.497">
<FIS_Sub_Author First_Name="Pit" Last_Name="LTD" />
</FIS_Sub_Article>
<FIS_Sub_Article Article_Title="Testing Abstracts" Created_Date="2010-01-15T15:31:54.497">
<FIS_Sub_Author First_Name="Pit" Last_Name="LTD" />
</FIS_Sub_Article>
<FIS_Sub_Article Article_Title="Testing Abstracts" Created_Date="2010-01-15T15:31:54.497">
<FIS_Sub_Author First_Name="Aneesh" Last_Name="Salim" />
</FIS_Sub_Article>
</FIS_EventType>
</Event>

i want to get the o/ p like this

<Event>
<FIS_EventType Event_Type="Conference">
<FIS_Sub_Article Article_Title="Conference Event By Aneesh" Created_Date="2010-01-15T14:36:16.043">
<FIS_Sub_Author First_Name="Aneesh" Last_Name="Salim" />
<FIS_Sub_Author First_Name="Kripa" Last_Name="V" />
<FIS_Sub_Author First_Name="Sajid" Last_Name="PC" />
<FIS_Sub_Author First_Name="Aneesh" Last_Name="Salim" />
</FIS_Sub_Article>
<FIS_Sub_Article Article_Title="Conference Event Article Title" Created_Date="2010-01-15T15:19:08.053">
<FIS_Sub_Author First_Name="Kripa" Last_Name="V" />
<FIS_Sub_Author First_Name="Sajid" Last_Name="PC" />
<FIS_Sub_Author First_Name="Pit" Last_Name="LTD" />
<FIS_Sub_Author First_Name="Pit" Last_Name="LTD" />
</FIS_Sub_Article>
<FIS_Sub_Article Article_Title="Testing Abstracts" Created_Date="2010-01-15T15:31:54.497">
<FIS_Sub_Author First_Name="Aneesh" Last_Name="Salim" />
</FIS_Sub_Article>
</FIS_EventType>
</Event>

the code which i have done is

ALTER PROCEDURE [dbo].[FSP_Event_GetAllAbstractsByEventID]
@Event_ID INT,
@Article_Type_ID INT
AS
BEGIN
SELECT FIS_EventType.Event_Type,
FIS_Sub_Article.Article_Title,
FIS_Sub_Article.Created_Date,
FIS_Sub_Author.First_Name,
FIS_Sub_Author.Last_Name

FROM FIS_Event
INNER JOIN FIS_EventType ON FIS_Event.Event_Type_ID = FIS_EventType.Event_Type_ID
INNER JOIN FIS_Sub_Article ON FIS_Event.Event_ID = FIS_Sub_Article.Event_ID
INNER JOIN FIS_Sub_Author ON FIS_Sub_Article.Sub_ID = FIS_Sub_Author.Sub_ID where FIS_Event.Event_ID=@Event_ID AND FIS_Sub_Article.Article_Type_ID=@Article_Type_ID
--order by FIS_Sub_Author.First_Name desc
FOR XML AUTO, ROOT('Event')
END

Regards,
Divya

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-15 : 06:48:04

ihave done like this but its throwing error
SELECT FIS_EventType.Event_Type,
FIS_Sub_Article.Article_Title,
FIS_Sub_Article.Created_Date,
FIS_Sub_Author.First_Name,
FIS_Sub_Author.Last_Name

FROM FIS_Event
INNER JOIN FIS_EventType ON FIS_Event.Event_Type_ID = FIS_EventType.Event_Type_ID
INNER JOIN FIS_Sub_Article ON FIS_Event.Event_ID = FIS_Sub_Article.Event_ID
INNER JOIN FIS_Sub_Author ON FIS_Sub_Article.Sub_ID = FIS_Sub_Author.Sub_ID where FIS_Event.Event_ID=@Event_ID AND FIS_Sub_Article.Article_Type_ID=@Article_Type_ID
order by FIS_Sub_Article.Article_Title
FOR XML AUTO, ROOT('Event')

END
the error is
Msg 306, Level 16, State 2, Procedure FSP_Event_GetAllAbstractsByEventID, Line 23
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Regards,
Divya

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-15 : 07:18:40

if added oder by FIS_Sub_Article.Article_Title

im getting error like this
Msg 306, Level 16, State 2, Procedure FSP_Event_GetAllAbstractsByEventID, Line 23
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
how i can resolve this

Regards,
Divya

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-01-15 : 07:40:16

If FIS_Sub_Article.Article_Title is a text field (why would you put a title as a text field?!?), CONVERT it to a varchar or nvarchar within your select.


SELECT FIS_EventType.Event_Type,
CONVERT(NVARCHAR(4000),FIS_Sub_Article.Article_Title) AS Article_Title, 
...

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-15 : 07:55:15

at last i got it

SELECT FIS_EventType.Event_Type ,
convert(varchar(max),FIS_Sub_Article.Article_Title) ArticleTitle,
FIS_Sub_Article.Created_Date ,
FIS_Sub_Author.First_Name ,
FIS_Sub_Author.Last_Name

FROM FIS_Event
INNER JOIN FIS_EventType ON FIS_Event.Event_Type_ID = FIS_EventType.Event_Type_ID
INNER JOIN FIS_Sub_Article ON FIS_Event.Event_ID = FIS_Sub_Article.Event_ID
INNER JOIN FIS_Sub_Author ON FIS_Sub_Article.Sub_ID = FIS_Sub_Author.Sub_ID where FIS_Event.Event_ID=@Event_ID AND FIS_Sub_Article.Article_Type_ID=@Article_Type_ID
group by FIS_Sub_Article.Created_Date,FIS_Sub_Author.First_Name,FIS_Sub_Author.Last_Name,FIS_EventType.Event_Type,convert(varchar(max),FIS_Sub_Article.Article_Title)
FOR XML AUTO, ROOT('Event')
--FOR XML raw'event'

END

thank you RickD

Regards,
Divya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources