XML from SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

XML from SQL

Author

Topic

cardgunner

326 Posts

Posted - 2007-07-20 : 11:25:48

Is there a way the create an XML file from SQL?

Card Gunner

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-20 : 13:41:24

you can use a FOR XML clause on the end of your query. 2000 supports these:

FOR XML AUTO
FOR XML RAW
FOR XML EXPLICIT -- this last one is a nasty beast.

in 2005 there is the much nicer FOR XML PATH

elsasoft.org

cardgunner

326 Posts

Posted - 2007-07-20 : 14:08:46

Thanks, I did try that however the resulting structure is not was I was looking for:


<tcisli205100><t_scmp>100</t_scmp><t_sfcp>100</t_sfcp><t_ityp>  S</t_ityp><t_idoc>10000003</t_idoc><t_brid>193</t_brid><t_doct>1</t_doct><t_stat>6</t_stat><t_cpay>N30</t_cpay><t_ccrs>   </t_ccrs><t_paym> CK</t_paym><t_ofbp>22928    </t_ofbp><t_itbp>22928  
</t_d1dt><t_d2ai>0.000000000000000e+000</t_d2ai><t_d2ah_1>0.000000000000000e+000</t_d2ah_1><t_d2ah_2>0.000000000000000e+000</t_d2ah_2><t_d2ah_3>0.000000000000000e+000</t_d2ah_3><t_d2dt>1970-01-01T00:00:00</t_d2dt><t_d3ai>0.000000000000000e+000</t_d3ai><t_d
0000000000e+000</t_amth_2><t_amth_3>0.000000000000000e+000</t_amth_3><t_tbai>4.

that is a small piece,

What am I doing wrong.

last clause in my query is
for xml auto, elements

Card Gunner

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-20 : 14:19:10

so what are you looking for then? I'm not going to guess!

elsasoft.org

cardgunner

326 Posts

Posted - 2007-07-20 : 14:25:10

Oh, sorry.


 <TempPoint2Export>
             <AvailabilityStatus>Sold</AvailabilityStatus>
             <UnitNumber>AU200349 </UnitNumber>
             <Year>0</Year>
             <Manufacturer>Miscellaneous</Manufacturer>
             <Model>TRUCK</Model>
             <Hours>0</Hours>
             <Serial_x0023_>CHARLIE TO GET</Serial_x0023_>
             <Branch>Detroit</Branch>
             <Features>WRIGHT</Features>
             <PreownedStatus>Used</PreownedStatus>
             <Type>TRUCK</Type>
             <ForSale>Yes</ForSale>
             <SalesCost>0</SalesCost>
             <Price>0</Price>
             <InvoiceDate>01/15/2007</InvoiceDate>
             <AcquisitionDate>05/29/2003</AcquisitionDate>
             <TotalCost>1800</TotalCost>
       </TempPoint2Export>

This is the structure they want it sent in.

Card Gunner

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-20 : 16:37:38

and what do your tables look like? post the schema for each table involved, and the query you have so far.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources