| Author |
 Topic  |
|
|
divyaram
Posting Yak Master
India
177 Posts |
 Posted - 01/18/2010 : 05:51:01
|
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
|
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 |
 |
|
| |
Topic |
|
|
|
XML help
