Fetch data from XML – XMLGet function using with Attribute Name and its value in Snowflake

I have an xml which I need to read using Snowflake SQL. I need to use the attribute name and lits value to fetch the data. For example -> id and “bk101” to fetch the contents.

<catalog issue="spring">
  <Books>
    <book id="bk101">The Good Book</book>
    <book id="bk102">The OK Book</book>
    <book id="bk103">The NOT Ok Book</book>
    <book id="bk104">All OK Book</book>
    <book id="bk105">Every OK Book</book>
  </Books>
</catalog>
<catalog issue="spring">
  <Books>
    <book id="bk102">The OK Book1</book>
    <book id="bk103">The NOT Ok Book1</book>
    <book id="bk104">All OK Book1</book>
  </Books>
</catalog>
<catalog issue="spring">
  <Books>
    <book id="bk101">The Good Book2</book>
    <book id="bk103">The NOT Ok Book2</book>
    <book id="bk104">All OK Book2</book>
    <book id="bk105">Every OK Book2</book>
  </Books>
</catalog>

CREATE TABLE BooksXML
(
    xml VARIANT
);

SELECT * FROM BooksXML

I am currently using below query-

SELECT 
     XMLGET(XMLGET(xml,'Books'),'book',0):"$" :: VARCHAR(100)   bk101         
    ,XMLGET(XMLGET(xml,'Books'),'book',1):"$" :: VARCHAR(100)   bk102
    ,XMLGET(XMLGET(xml,'Books'),'book',2):"$" :: VARCHAR(100)   bk103
    ,XMLGET(XMLGET(xml,'Books'),'book',3):"$" :: VARCHAR(100)   bk104
    ,XMLGET(XMLGET(xml,'Books'),'book',4):"$" :: VARCHAR(100)   bk105
FROM BooksXML T1

In this case I am passing the index 0-4 to fetch the data is not producing correct results.

Instead of this I need to fetch the records using attribute name and its value(id=”bk101″)

Following is the result I am looking for

BK101,BK102,BK103,BK104,BK105
The Good Book,The OK Book,The NOT Ok Book,All OK Book,Every OK Book
NULL,The OK Book1,The NOT Ok Book1,All OK Book1,NULL
The Good Book2,NULL,The NOT Ok Book2,All OK Book2,Every OK Book2

Answer

This will get you the id and value for each book:

select xx.seq, xx.value:"@id" id, xx.value:"$" title
from BooksXML, table(flatten(xml:"$":"$")) xx

Then a pivot presents the results as desired:

select * 
from (
    select xx.seq, xx.value:"@id" id, xx.value:"$" title
    from BooksXML, table(flatten(xml:"$":"$")) xx
)
pivot(max(title) for id in ('bk101', 'bk102', 'bk103', 'bk104', 'bk105')) as p
order by seq

Table setup:

CREATE temp TABLE BooksXML
as
select parse_xml('<catalog issue="spring">
  <Books>
    <book id="bk101">The Good Book</book>
    <book id="bk102">The OK Book</book>
    <book id="bk103">The NOT Ok Book</book>
    <book id="bk104">All OK Book</book>
    <book id="bk105">Every OK Book</book>
  </Books>
</catalog>') xml
union all select parse_xml('
<catalog issue="spring">
  <Books>
    <book id="bk102">The OK Book1</book>
    <book id="bk103">The NOT Ok Book1</book>
    <book id="bk104">All OK Book1</book>
  </Books>
</catalog>')
union all select parse_xml('
<catalog issue="spring">
  <Books>
    <book id="bk101">The Good Book2</book>
    <book id="bk103">The NOT Ok Book2</book>
    <book id="bk104">All OK Book2</book>
    <book id="bk105">Every OK Book2</book>
  </Books>
</catalog>');