Solved

Problem with For XML Explicit formatting

Posted on 2008-06-11
5
173 Views
Last Modified: 2010-03-19
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>
0
Comment
Question by:drumph
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 21762243
can you provide your full query for us?
0
 

Author Comment

by:drumph
ID: 21762358
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')

0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 21762918
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.
0
 

Author Comment

by:drumph
ID: 21763149
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
0
 

Accepted Solution

by:
drumph earned 0 total points
ID: 21771154
This is the solution I came up with:

declare @authors table
( Author_ID integer,
  Author_name varchar(15))
insert into @authors
select 150, 'Bell' union all
select 151, 'James' union all
select 152, 'Henry'
--select * from @authors

 

declare @books table
( Author_ID integer,
  Name varchar(15)
)
insert into @books
select 150, 'Bell_book1' union all
select 150, 'Bell_book2' union all
select 151, 'James_book1' union all
select 151, 'James_book2' union all
select 152, 'Henry_book1'
--select * from @books

 
select
a.Author_name as [Author_name],
a.Author_id as [Author_ID],
( select
b.Name as [Books/Name]
from @books b
where a.author_id = b.author_id
for xml path(''), type
)
from @authors a
for xml auto, root ('Library'), elements
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question