Return a resultset to XML?

Hello I am trying to pass a recordset to XML
Database: pubs
select * from titles for xml auto
but it returns me this
<titles title_id="BU1032" title="The Busy Executive's Database Guide"
type="business " pub_id="1389" price="20.0000" advance="5000.0000"
royalty="10" ytd_sales="4095" notes="An overview of available database
systems with emphasis on common business
royalty="16" ytd_sales="8780" notes="A survey of software for the naive
user, focusing on the 'friendliness' of each."
pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888" title="Secrets of
Silicon Valley" type="popular_comp" pub_id="1389" pr
"psychology " pub_id="0736" price="8.0000" advance="4000.0000" royalty="10"
ytd_sales="3336" notes="Protecting yourself and your loved ones from undue
emotional stress in the modern world. Use of computer and nutritional aids
emphasized." pubdate="1991-06
I dont want that way
I would like more or less
<title>title1</title>
<type>type</type>
and so on.
ThanksHi
Look at the FOR EXPLICITY clause in books online and you may be able to do
what you require.
John
"Luis Esteban Valencia" wrote:
> Hello I am trying to pass a recordset to XML
> Database: pubs
> select * from titles for xml auto
> but it returns me this
> <titles title_id="BU1032" title="The Busy Executive's Database Guide"
> type="business " pub_id="1389" price="20.0000" advance="5000.0000"
> royalty="10" ytd_sales="4095" notes="An overview of available database
> systems with emphasis on common business
> royalty="16" ytd_sales="8780" notes="A survey of software for the naive
> user, focusing on the 'friendliness' of each."
> pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888" title="Secrets of
> Silicon Valley" type="popular_comp" pub_id="1389" pr
> "psychology " pub_id="0736" price="8.0000" advance="4000.0000" royalty="10"
> ytd_sales="3336" notes="Protecting yourself and your loved ones from undue
> emotional stress in the modern world. Use of computer and nutritional aids
> emphasized." pubdate="1991-06
>
> I dont want that way
> I would like more or less
> <title>title1</title>
> <type>type</type>
> and so on.
> Thanks
>
>|||"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> Hello I am trying to pass a recordset to XML
> Database: pubs
> select * from titles for xml auto
> but it returns me this
> <titles title_id="BU1032" title="The Busy Executive's Database
> Guide" type="business " pub_id="1389" price="20.0000"
> advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> of available database systems with emphasis on common business
> royalty="16" ytd_sales="8780" notes="A survey of software for the
> naive user, focusing on the 'friendliness' of each."
> pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> loved ones from undue emotional stress in the modern world. Use of
> computer and nutritional aids emphasized." pubdate="1991-06
>
> I dont want that way
> I would like more or less
> <title>title1</title>
> <type>type</type>
> and so on.
> Thanks
>
>
select * from titles for xml auto, elements
--
Regards
JTC ^..^|||It doesnt return good. it seems to cut the strings
look at this
<titles><title_id>BU1032</title_id><title>The Busy Executive's Database
Guide</title><type>business
</type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
/price><
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
> "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
> news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> > Hello I am trying to pass a recordset to XML
> > Database: pubs
> >
> > select * from titles for xml auto
> > but it returns me this
> > <titles title_id="BU1032" title="The Busy Executive's Database
> > Guide" type="business " pub_id="1389" price="20.0000"
> > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> > of available database systems with emphasis on common business
> > royalty="16" ytd_sales="8780" notes="A survey of software for the
> > naive user, focusing on the 'friendliness' of each."
> > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> > loved ones from undue emotional stress in the modern world. Use of
> > computer and nutritional aids emphasized." pubdate="1991-06
> >
> >
> > I dont want that way
> > I would like more or less
> > <title>title1</title>
> > <type>type</type>
> >
> > and so on.
> >
> > Thanks
> >
> >
> >
> select * from titles for xml auto, elements
> --
> Regards
> JTC ^..^|||It is only Query Analyzer. You can configure where it cut a column (max is 8000). All xml is one
column in QA.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in message
news:%23eMjeSlgFHA.3436@.tk2msftngp13.phx.gbl...
> It doesnt return good. it seems to cut the strings
> look at this
> <titles><title_id>BU1032</title_id><title>The Busy Executive's Database
> Guide</title><type>business
> </type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
> e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
> /price><
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
> news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
>> "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
>> news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
>> > Hello I am trying to pass a recordset to XML
>> > Database: pubs
>> >
>> > select * from titles for xml auto
>> > but it returns me this
>> > <titles title_id="BU1032" title="The Busy Executive's Database
>> > Guide" type="business " pub_id="1389" price="20.0000"
>> > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
>> > of available database systems with emphasis on common business
>> > royalty="16" ytd_sales="8780" notes="A survey of software for the
>> > naive user, focusing on the 'friendliness' of each."
>> > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
>> > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
>> > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
>> > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
>> > loved ones from undue emotional stress in the modern world. Use of
>> > computer and nutritional aids emphasized." pubdate="1991-06
>> >
>> >
>> > I dont want that way
>> > I would like more or less
>> > <title>title1</title>
>> > <type>type</type>
>> >
>> > and so on.
>> >
>> > Thanks
>> >
>> >
>> >
>> select * from titles for xml auto, elements
>> --
>> Regards
>> JTC ^..^
>|||Hi
If you still have problems cutting the tags then put in dummy data values
that contain a carriage return. See http://tinyurl.com/e4rla
John
"Luis Esteban Valencia" wrote:
> It doesnt return good. it seems to cut the strings
> look at this
> <titles><title_id>BU1032</title_id><title>The Busy Executive's Database
> Guide</title><type>business
> </type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
> e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
> /price><
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
> news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
> > "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
> > news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> >
> > > Hello I am trying to pass a recordset to XML
> > > Database: pubs
> > >
> > > select * from titles for xml auto
> > > but it returns me this
> > > <titles title_id="BU1032" title="The Busy Executive's Database
> > > Guide" type="business " pub_id="1389" price="20.0000"
> > > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> > > of available database systems with emphasis on common business
> > > royalty="16" ytd_sales="8780" notes="A survey of software for the
> > > naive user, focusing on the 'friendliness' of each."
> > > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> > > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> > > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> > > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> > > loved ones from undue emotional stress in the modern world. Use of
> > > computer and nutritional aids emphasized." pubdate="1991-06
> > >
> > >
> > > I dont want that way
> > > I would like more or less
> > > <title>title1</title>
> > > <type>type</type>
> > >
> > > and so on.
> > >
> > > Thanks
> > >
> > >
> > >
> >
> > select * from titles for xml auto, elements
> >
> > --
> > Regards
> > JTC ^..^
>
>