asked on

Problem with For XML Explicit formatting

I am having a problem formating the XML structure for my SQL query. I am using FOR XML EXplicit clause and Auto is not an options.

I have provided an excerpt of my query with its results. There is no linkage between the Author and book data.

Author.author_name as [Authors!1!Author_name]
Author.Author_id as [Authors!1!Author_id]
Book.name as [Books!2!name!element]
Book.author_id as [Books!2!author_id!element]

<Authors Author_name="Bell" Author_ID="150"/>
<Authors Author_name="James" Author_ID="151"/>
<Authors Author_name="Henry" Author_ID="152"/>
<Books>
<Name>Bell_book1</Name>
<Author_ID>150</Author_ID>
</Books>
<Books>
<name>James_book2</Name>
<Author_ID>151</Author_ID>
</Books>
<Books>
<Name>Bell_book1</Name>
<Author_ID>150</Author_ID>
</Books>
<Books>
<name>Henry_book2</Name>
<Author_ID>152</Author_ID>
</Books>

Ideally I want it to looking like my XML example below. Does anyone have any suggestion for FOR XML syntax modifications or can this be done at all.

<Authors Author_name="Bell" Author_ID="150"/>
<Books>
<Name>Bell_book1</Name>
</Books>
<Books>
<name>Bell_book2</name>
</Books>

<Authors Author_name="James" Author_ID="151"/>
<Books>
<Name>James_book1</Name>
</Books>
<Books>
<name>James_book2</Name>
</Books>

Daniel Reynolds

can you provide your full query for us?

drumph

ASKER

please see query below

select
1 as tag,
null as parent,
Author.author_name as [Authors!1!Author_name],
Author.Author_id as [Authors!1!Author_id],
Book.name as [Books!2!name!element],
Book.author_id as [Books!2!author_id!element],

from authors
join books on authors.author_id = books.authors_id

Union

select
2 as tag,
1 as parent,
Author.author_name,
Author.Author_id,
Book.name,
Book.author_id,

from authors
join books on authors.author_id = books.authors_id

For xml explicit , root ('my_books')

Daniel Reynolds

The following query returns closer to what you want and eliminates your union.

select
1 as tag,
null as parent,
Author.au_lname as [Authors!1!Author_name],
Author.Au_id as [Authors!1!Author_id],
Book.name as [Authors!1!Book!element]--,
--Book.author_id as [Authors!1!author_id!element]

from authors author
join books book on author.au_id = book.author_id

For xml explicit

One of the things we have done in the past is pull the related data bits into a temp table that is laid out more closely to what you need the results to be. And then pull the info from the temp table.

drumph

ASKER

This looks a little better.. still repeating values.. and no nesting.
But what is I want nested Nodes?Author will all related books under also without repeating the Author name.

Author -> Bil
<book1
<book2

ASKER CERTIFIED SOLUTION

drumph

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial