Help with xml query
-
Hello friends, i am new in xml and i need to make a xml from a query
i have a table like this
Names_Table
id name lastname
1 Jhon Smith
2 Paul Jackson
and i need generate something like
<table>
<row>
<id>1</id>
<name>Jhon</name>
<lastname>Smith</lastname>
</row>
<row>
<id>2</id>
<name>Paul</name>
<lastname>Jackson</lastname>
</row>
</table>
i am trying to use " for xml explicit" but display all xml in a single line
i am using sqlserver 2000 thanks for your help
-
See if this is what you want. To read the XML in an easier way run the query into a grid then click on the XML and it will be readable (this from the SQL 2005 query window, this does not work in 2000 Query Analyzer). The way it looks is one big line of text.
create
table #t (
id
int,
Fname varchar
(20),
LName varchar
(20)
)
insert
into #t values (1, 'john','smith')
insert
into #t values (2, 'Paul','Jackson')
select
1
as Tag,
null as Parent,
ID
as [Empl!1!ID],
null as [Name!2!Fname!ELEMENT],
null as [Name!2!Lname!ELEMENT]
from
#t
union
all
select 2 as Tag,
1
as Parent,
ID
,
Fname
,
Lname
from
#T
order by [Empl!1!ID], [Name!2!Lname!ELEMENT]
for
XML Explicit
-- Edit: order by needs to be in there
-
the result was
<Empl ID="1"><Name><Fname>john</Fname><Lname>smith</Lname></Name></Empl><Empl ID="2"><Name><Fname>Paul</Fname><Lname>Jackson</Lname></Name></Empl>
and i seek the next format
<table>
<row>
<id>1</id>
..................
......
</row>
</table>
-
Only way *I* have found to do close to what you want is using the for XML path, but I do not think that is available in SQL 2000.
Doing
select * from #T for XML path
gives me:
<
row>
<
id>1</id>
<
Fname>john</Fname>
<
LName>smith</LName>
</
row>
<
row>
<
id>2</id>
<
Fname>Paul</Fname>
<
LName>Jackson</LName>
</
row>
-
No it is not avaible but i created a sp it seems the only way i think, only it works for a physic table
example :
create table t (
id int,
Fname varchar(20),
LName varchar(20)
)
insert into t values (1, 'john','smith')
insert into t values (2, 'Paul','Jackson')
exec cp_creaxml 't','id'
------------------------------------------------------------
<table>
<row>
<id>1</id>
<Fname>john</Fname>
</row>
<row>
<id>2</id>
<Fname>Paul</Fname>
</row>
</table>
---------------------------------------------------------------------
The code :
CREATE proc CP_CreaXml
@table_name varchar(255),
@id_name varchar(255)
as
set nocount on
declare @max_col int
declare @table_id int
declare @this_col int
declare @col_list varchar(8000)
declare @elemento varchar(500)
declare @query varchar(500)
declare @valor varchar (500)
declare @id int
declare @COLUMNA varchar(500)
select @table_id = id from sysobjects
where name = @table_name
select @max_col = max(colid) from syscolumns
where id = @table_id
create table #table (dato varchar(800) )
create table #table2 (dato varchar(800) )
set @query = ' insert into #table2 select ' + @id_name + ' from ' + @table_name
exec (@query)
DECLARE Cursor_indice CURSOR KEYSET FOR
select * from #table2
open Cursor_indice
set @id = null
FETCH NEXT FROM Cursor_indice INTO @id
print '<table>'
WHILE @@FETCH_STATUS = 0
BEGIN -- Comienzo del Bucle
print ' <row>'
select @this_col = min(colid) from syscolumns
where id = @table_id
set @col_list = ''
while @this_col != @max_col
begin
select @COLUMNA = name from syscolumns
where id = @table_id
and colid = @this_col
set @query = ' insert into #table select ' + @COLUMNA + ' from '+ @table_name + ' where ' + @id_name + ' = ' + convert(varchar,@id)
--print @query
exec (@query)
select @valor = dato from #table
TRUNCATE TABLE #table
if isnull(@valor,'') = ''
begin
select @elemento = ' <' + @COLUMNA + '/>'
end
else
begin
select @elemento = ' <' + @COLUMNA + '>' + @valor + '</' + @COLUMNA + '>'
end
print @elemento
set @this_col = @this_col + 1
end
set @id = null
FETCH NEXT FROM Cursor_indice INTO @id
print ' </row>'
END
print '</table>'
close Cursor_indice
deallocate Cursor_indice
drop table #table
drop table #table2
---------------------------------------------------------------------------
Hey , some names are in spanish because i am from Chile
but i am seeking to create the xml using a single query but it seems that is not possible in sql server 2000 thank you anyway
-
Hector,
if you need 3 level XML then you need 3 tags.
Just add another tag to the example given and you are there.
I slightly modified that script to bring you exactly what you're expecting:
IF Object_ID('tempdb..#T') IS NOT NULL
DROP TABLE #T
create table #t (
id int,
Fname varchar(20),
LName varchar(20)
)
insert into #t values (1, 'john','smith')
insert into #t values (2, 'Paul','Jackson')
select
1 as Tag,
null as Parent,
NULL as [Table!1!Name!ELEMENT],
NULL as [row!2!No!ELEMENT],
NULL as [Empl!3!ID!ELEMENT],
null as [Empl!3!Fname!ELEMENT],
null as [Empl!3!Lname!ELEMENT]
union all
select
2 as Tag,
1 as Parent,
NULL as [Table!1!Name!ELEMENT],
NULL as [row!2!No!ELEMENT],
NULL as [Empl!3!ID!ELEMENT],
null as [Empl!3!Fname!ELEMENT],
null as [Empl!3!Lname!ELEMENT]
union all
select 3 as Tag,
2 as Parent,
NULL as [Table!1!Name!ELEMENT],
NULL as [row!2!No!ELEMENT],
ID,
Fname,
Lname
from #T
order by [Tag], [Empl!3!ID!ELEMENT], [Empl!3!Lname!ELEMENT]
for XML Explicit
DROP TABLE #T
_____________
Code for TallyGenerator -
Cool thank you
but do you know if is possible to display in a order way in the results of query analizer because the xml displayes in a only single line<Table><row><ID>1</ID><Fname>john</Fname><Lname>smith</Lname></row><row><ID>2</ID><Fname>Paul</Fname><Lname>Jackson</Lname></row></Table>
and i want something like this
<table>
<row>
<id>1</id>
<Fname>john</Fname>
</row>
<row>
<id>2</id>
<Fname>Paul</Fname>
</row>
</table>
well i will search to do it thanks a lot of
-
QA is not XML reading or editing tool.
SQL Server generated XML for you.
Use proper tool to display it or work it out.
_____________
Code for TallyGenerator -
Ok you are right one cannt gain everything in the life
thank you
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply