CODEWITHSUNDEEP
sqlsql-serverxmlselectsqlxml
Sharles Phirans
Sharles Phirans

Reputation: 93

Formatting XML into a select statement

DECLARE @Data AS XML = '
<Catalog>
<Artist>
    <name>Wolfgang</name>
    <name>Razorback</name>
</Artist>
<Album>
    <Category>Rock</Category>
    <Category>Alternative</Category>
</Album>
</Catalog>'

SELECT 
   B.value('(text())[1]','varchar(15)') as Artist,
   C.value('(text())[1]','varchar(15)') as Album
FROM  @Data.nodes('/Catalog') AS Catalog(A)
  CROSS APPLY A.nodes('Artist/name') as Artist(B)
  CROSS APPLY A.nodes('Album/Category') as Album(C)

I was expecting that the result should be like this, I realy just like to make the Artist as the first colum and Album as the second column

enter image description here

but it returned this query,

enter image description here

Upvotes: 1

Views: 80

Answers (1)

roman
roman

Reputation: 117345

your xml is not very good. you can do this:

;with cte_artist as (
    select
        row_number() over(order by (select '1')) as rn,
        T.C.value('text()[1]','varchar(15)') as Artist
    from @Data.nodes('/Catalog/Artist/name') as T(C)
), cte_album as (
    select
        row_number() over(order by (select '1')) as rn,
        T.C.value('text()[1]','varchar(15)') as Album
    from @Data.nodes('/Catalog/Album/Category') as T(C)
)
select
    a1.Artist,
    a2.Album
from cte_artist as a1
    full outer join cte_album as a2 on a2.rn = a1.rn

sql fiddle demo

But better way would be to create xml like this (or any other nested way you want):

<Catalog>
    <Album name="Rock album">
        <Category>Rock</Category>
        <Artist name="Wolfgang"/>
    </Album>
    <Album name="Alternative album">
        <Category>Alternative</Category>
        <Artist name="Razorback"/>
    </Album>
</Catalog>

So you could query it like this:

select
    T.C.value('@name','varchar(15)') as name,
    T.C.value('(Category/text())[1]','varchar(15)') as Category,
    T.C.value('Artist[1]/@name','varchar(15)') as Artist
from @Data.nodes('/Catalog/Album') as T(C)

Upvotes: 2

Related Questions