Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

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>
0
drumph
Asked:
drumph
  • 3
  • 2
1 Solution
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
can you provide your full query for us?
0
 
drumphAuthor Commented:
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
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
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
 
drumphAuthor Commented:
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
 
drumphAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now