XML question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

XML question

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-27 : 12:01:20

Greetings

Please let me know is this is the wrong post.
I have a table with following fields

ID BibleBookNumber BibleChapter BibleVerse BibleCitation
1  1               1            1          In the beginning ...

I want to dump this into an XML file. How can I do this so that the final result will look as follows or any other better way you suggest.

<xml>
<BibleBookNumber Number=1>
   <BibleChapter Chapter=1>
      <BibleVerse  Verse=1>In the beginning ...
      <BibleVerse  Verse=2>And the Earth was...
   </BibleChapter>
   <BibleChapter Chapter=2>
      <BibleVerse  Verse=1>Adam...
      <BibleVerse  Verse=2>Eve...
   </BibleChapter>
</BibleBookNumber>

I want to be able to dump out the whole Holy Book out into an xml file.

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-27 : 22:44:12

Probably the easiest way to do this is using xml path, but xml explicit gives you more flexibility, albeit allegedly harder to write, but allowing you to produce the xml exactly the way you specified. Here is some untested code using xml explicit.

select 
   1    as Tag,
   null as Parent,
   BibleBookNumber as [BibleBookNumber!1!Number],
   null as [BibleChapter!2!Chapter],
   null as [BibleVerse!3!Verse],
   null as [BibleVerse!3]
from   YourTable
union
select 
   2 as Tag,
   1 as Parent,
   BibleBookNumber,
   BibleChapter,
   null,
   null
from YourTable
union
select
   3 as Tag,
   2 as Parent,
   BibleBookNumber,
   BibleChapter,
   BibleVerse,
   BibleCitation
from 
   YourTable
order by
   [BibleBookNumber!1!Number],
   [BibleChapter!2!Chapter],
   [BibleVerse!3!Verse]
for xml explicit

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-02 : 19:49:53

wow this is awesome , i tried the explicit thing but was hitting a wall.
This is perfect!

Thank you very much!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-02 : 20:22:17

yvw, yosiasz.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources