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-18 : 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

180 Posts

Posted - 2010-01-18 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources