Re: Nested xml

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Feb 6, 9:57 am, Hilary Cotter <hilary.cot...@xxxxxxxxx> wrote:
Try this:

create table XMLNest(pk int not null, City varchar(200), State
varchar(200), Country varchar(200))
GO

insert into XMLNest values (1, 'Albany','NY','US')
insert into XMLNest values (2, 'Manhatten','NY','US')
insert into XMLNest values (3, 'Boonville','NY','US')
insert into XMLNest values (4, 'Newark','NJ','US')
insert into XMLNest values (5, 'Englishtown','NJ','US')
insert into XMLNest values (6, 'Rome','NY','US')
insert into XMLNest values (7, 'Rome','VA','VA')

select country,state, (select city from (select country, state, city
from XMLNest) as City
where XMLNest.Country=City.Country and XMLNest.State=City.State and
XMLNest.Country=City.Country for xml auto, type, elements)
from XMLNest
group by country, state
order by country
for xml auto, type

On Feb 6, 12:21 am, tew...@xxxxxxxxx wrote:

I'm having trouble getting the nested result (or tree structure) I'm
wanting. I have data which is similar in structure to a table with
columns "Country" "State" and "City". I'm wanting to get xml which
would have something like "U.S" once, "New York" several times, with
cities like "Albany" "New York" and so on appearing once for each
city. So basically I'm looking for a normal tree structure, like you
would expect with an XML document.

When I do the "for xml auto" SQL command (something like Select
Country, State, City from MyTable where Country = 'U.S.') I get back
XML which has "U.S." repeated, instead of appearing just once, as I
would expect.

The example fromhttp://www.topxml.com/sql/for_xml_auto.aspshowsa
nested result, using a query of " SELECT team.Sponsor, rider.RiderName
FROM Team, Rider WHERE team.TeamID = rider.TeamID FOR XML AUTO"

So how do I get my single table example to work? I can think of the
following possibilities:

a)Rewrite the query so it mimics a 2 table query
b)Restructure the data so it uses 2 tables instead of just 1.

Any suggestions?

Thanks.

I'm not terrible at SQL, but not great either. First of all, thanks
so much for the response.

I tried copying in the SQL you gave, and the table created fine, with
the data inserted, but the SQL statement failed, with the following
error message:

Line 4:Incorrect syntax near 'xml'.

Did you test the SQL you wrote? I'm trying to ascertain if the SQL,
as written, should work and there's something that needs to be figured
out as to why it's not working (having to do with my environment), of
if you simply wrote an example from your head, and it's not working
just because there's some syntax error.


Thanks again,

Tom
.

Relevant Pages

  • Re: Nested xml
    ... varchar, Country varchar) ... insert into XMLNest values ... XMLNest.Country=City.Country for xml auto, type, elements) ... I'm not terrible at SQL, ...
    (microsoft.public.sqlserver.xml)
  • Re: Nested xml
    ... create table XMLNest(pk int not null, City varchar(200), State ... insert into XMLNest values ... select country,state, (select city from (select country, state, city ... XMLNest.Country=City.Country for xml auto, type, elements) ...
    (microsoft.public.sqlserver.xml)
  • Re: Nested xml
    ... If it's erroring at the keyword "xml", he's probably running SQL 2000. ... varchar, Country varchar) ... insert into XMLNest values ...
    (microsoft.public.sqlserver.xml)
  • Re: Nested xml
    ... What version of SQL Server are you running? ... varchar, Country varchar) ... insert into XMLNest values ... XMLNest.Country=City.Country for xml auto, type, elements) ...
    (microsoft.public.sqlserver.xml)
  • Re: Nested xml
    ... create table XMLNest(pk int not null, City varchar(200), State ... varchar, Country varchar) ... insert into XMLNest values ... XMLNest.Country=City.Country for xml auto, type, elements) ...
    (microsoft.public.sqlserver.xml)