Creating XML structure with SQL Server

Question

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!

Are you using XML explicit? Can you post what you've tried and what the problem is? learn.microsoft.com/en-us/sql/relational-databases/xml/… — EdmCoff
– EdmCoff, Commented Sep 8, 2022 at 19:51
While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Sep 8, 2022 at 21:33

griv · Accepted Answer · 2022-09-09 15:24:00Z

1

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.

edited Sep 9, 2022 at 15:24

answered Sep 8, 2022 at 21:21

griv

2,2852 gold badges16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

griv Over a year ago

Thanks @Charlieface for the critique. Good point on the brackets, and I like the text() syntax, its a lot cleaner than what I had, allowing the XML path to store a value to keep track of the nested elements. I've updated my answer to reflect your comment.

Collectives™ on Stack Overflow

Creating XML structure with SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related