SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 XML help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divyaram
Posting Yak Master

India
177 Posts

Posted - 01/18/2010 :  05:51:01  Show Profile  Reply with Quote
hi all,


i got the output in xml like this


<JournalDetails>
<FIS_Journal_ArticleType Article_Type_ID="23" Journal_Article_Type_ID="65">
<FIS_ArticleType Article_Type_Name="Focused Review ">
<FIS_Journal_Repository Journal_Rep_ID="40" Repository_ID="2" />
</FIS_ArticleType>
</FIS_Journal_ArticleType>
</JournalDetails>

i want the underlined nodes to be like this

<FIS_Journal_ArticleType Article_Type_ID="23" Journal_Article_Type_ID="65" Article_Type_Name="Focused Review ">
</FIS_Journal_ArticleType>


the select statement i have written for this xml output is like this


SELECT
FIS_Journal_ArticleType.Article_Type_ID,
FIS_Journal_ArticleType.Journal_Article_Type_ID,
FIS_ArticleType.Article_Type_Name,
FIS_Journal_Repository.Journal_Rep_ID,
FIS_Journal_Repository.Repository_ID
FROM
FIS_ArticleType
INNER JOIN FIS_Journal_ArticleType ON FIS_Journal_ArticleType.Journal_ID= FIS_ArticleType.Journal_ID
INNER JOIN FIS_Journal_Repository ON FIS_Journal_Repository.Journal_ID= FIS_ArticleType.Journal_ID
WHERE
FIS_ArticleType.Journal_ID= @Journal_ID
for xml auto, root('JournalDetails')


Regards,
Divya

divyaram
Posting Yak Master

India
177 Posts

Posted - 01/18/2010 :  07:35:22  Show Profile  Reply with Quote
i got it
select((SELECT
FIS_ArticleType.Article_Type_Name,
FIS_Journal_ArticleType.Article_Type_ID,
FIS_Journal_ArticleType.Journal_Article_Type_ID
FROM
FIS_ArticleType
INNER JOIN FIS_Journal_ArticleType ON FIS_ArticleType.Article_Type_ID =FIS_Journal_ArticleType.Article_Type_ID
INNER JOIN FIS_Journal ON FIS_Journal.Journal_ID=FIS_Journal_ArticleType.Journal_ID
WHERE FIS_Journal.Journal_ID=@Journal_ID
FOR XML AUTO,TYPE
)AS ArticleType,
(SELECT
Journal_Rep_ID,
ISNULL(Repository_ID,'0') AS REP
FROM
FIS_Journal_Repository
INNER JOIN FIS_Journal ON FIS_Journal.Journal_ID= FIS_Journal_Repository.Journal_ID
WHERE FIS_Journal.Journal_ID=@Journal_ID)

Regards,
Divya
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000