Creating XML structure with SQL Server

Kawe 0
Viewed 56

I am trying to make the bible available in some versions of translations for a presentation software that uses files in .xml format.

I want to create xml file of this structure:

   <Bible>
     <book name="Gênesis" abbrev="gn" nchapters="50" >
       <chapter n="1">
          <verse n="1"> No princípio criou Deus o céu e a terra.</verse>
          <verse n="2"> A terra era sem forma e vazia; [...] a face das águas.</verse>
          <verse n="3"> Disse Deus: haja luz. E houve luz.</verse>
       </chapter>
       <chapter n="2">
     
         [...] 
     </book>
     <book name="Êxodo" abbrev="êx" nchapters="40" >
       <chapter n="1">
          <verse n="1"> No princípio criou Deus o céu e a terra.</verse>
          <verse n="2"> A terra era sem forma e vazia; [...] a face das águas.</verse>
          <verse n="3"> Disse Deus: haja luz. E houve luz.</verse>
     </chapter>
     <chapter n="2">

     [...]

    </book>

    [...]
   </Bible>

I have 2 tables: "Books", " Verses".

Books

Book_id name abbrev nchapters
1 Gênesis Gn 50
2 Êxodo Êx 40
... ... ... ...

Verses

id book chapter verse text
1 Gênesis 1 1 No princípio criou Deus os céus...
2 Gênesis 1 2 E a terra era sem forma e vazia...
... ... ... ... ...
1534 Êxodo 1 1 Estes pois são os nomes dos filhos...

I am not able to write a code in the SQL query that generates this structure. Any help in getting code that solves this question will be highly appreciated.

Thanks!

xml sql-server sql
1 Answers

You can use FOR XML to generate XML with subqueries for each of your nested elements and attributes.

    select
    b.name as [@name],
    b.abbrev as [@abbrev],
    b.chapters as [@nchapters],
    (select 
        c.chapter as [@n],
        (select 
           v.verse as [@n],
           v.text as [text()] 
           from Verses v 
           where v.book = b.name
           and v.chapter = c.chapter
           for xml path('verse'), TYPE
          )
         from Verses c 
         where c.book = b.name
         group by c.chapter
         for xml path('chapter'), TYPE
    )
from Books b
for xml path('book'), TYPE, ROOT('Bible')

Input:

Book_id name abbrev chapters id book chapter verse text
1 Gênesis Gn 50 1 Gênesis 1 1 No princípio criou Deus os céus...
1 Gênesis Gn 50 2 Gênesis 1 2 E a terra era sem forma e vazia...
1 Gênesis Gn 50 3 Gênesis 2 1 Test Verse 1...
2 Êxodo Êx 40 4 Êxodo 1 1 Test Verse 2...

Output:

<Bible>
    <book name="Gênesis" abbrev="Gn" nchapters="50">
        <chapter n="1">
            <verse n="1">No princípio criou Deus os céus...</verse>
            <verse n="2">E a terra era sem forma e vazia...</verse>
        </chapter>
        <chapter n="2">
            <verse n="1">Test Verse 1...</verse>
        </chapter>
    </book>
    <book name="Êxodo" abbrev="Êx" nchapters="40">
        <chapter n="1">
            <verse n="1">Test Verse 2...</verse>
        </chapter>
    </book>
</Bible>

db<>fiddle here.

GRIV0
Related