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.
| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2010-10-28 : 09:28:44
|
| Dear All,I am new for SQL XML programming I want to extract data from SQL tables to XML file in the E2B standards.the source will be the number of tables with selected columns and out put should be XML file.How can we do this in SQL server 2005 Enterprise Edition with 32 bit.Your sharing will be highly appreciable.The XML format will be like below. <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE ichicsr (View Source for full doctype...)> - <ichicsr lang="en">- <ichicsrmessageheader> <messagetype>ichicsr</messagetype> <messageformatversion>2.1</messageformatversion> <messageformatrelease>2.0</messageformatrelease> <messagenumb>56530_PRD</messagenumb> <messagesenderidentifier>MPIGALT</messagesenderidentifier> <messagereceiveridentifier>GALTJNJPRD</messagereceiveridentifier> <messagedateformat>204</messagedateformat> <messagedate>20100907112552</messagedate> </ichicsrmessageheader>- <safetyreport> <safetyreportversion>3</safetyreportversion> <safetyreportid>ES-MPIJNJ-2007_02502</safetyreportid> <primarysourcecountry>ES</primarysourcecountry> <occurcountry>ES</occurcountry> <transmissiondateformat>102</transmissiondateformat> <transmissiondate>20100903</transmissiondate> <reporttype>3</reporttype> <serious>1</serious> <seriousnessdeath>2</seriousnessdeath> <seriousnesslifethreatening>2</seriousnesslifethreatening> <seriousnesshospitalization>2</seriousnesshospitalization> <seriousnessdisabling>2</seriousnessdisabling> <seriousnesscongenitalanomali>2</seriousnesscongenitalanomali> <seriousnessother>1</seriousnessother> <receivedateformat>102</receivedateformat> <receivedate>20070719</receivedate> <receiptdateformat>102</receiptdateformat> <receiptdate>20100903</receiptdate> <additionaldocument>2</additionaldocument> <fulfillexpeditecriteria>1</fulfillexpeditecriteria> <companynumb>eee</companynumb> <duplicate>1</duplicate> <medicallyconfirm>1</medicallyconfirm> - <summary> <narrativeincludeclinical>xxxxxxx.</narrativeincludeclinical> <reportercomment /> <senderdiagnosismeddraversion>13.0</senderdiagnosismeddraversion> <senderdiagnosis>10028651</senderdiagnosis> </summary> </patient> </safetyreport> </ichicsr>Thanks,Gangadhar |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-10-28 : 13:10:47
|
Explore the "FOR XML" query option clause.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-10-29 : 00:53:50
|
| If you please provide with some details will be great help.Thanks,Gangadhar |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-10-29 : 01:19:23
|
See http://msdn.microsoft.com/en-us/library/ms187082(SQL.90).aspx
N 56°04'39.26"E 12°55'05.63" |
|
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-10-29 : 03:47:36
|
| Thank you very much Peso |
|
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-10-30 : 13:45:45
|
| Hi Peso,In the above link provided i did't find much info.Here my intension is to have the out put in this format <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE ichicsr (View Source for full doctype...)> - <ichicsr lang="en">- <ichicsrmessageheader><messagetype>ichicsr</messagetype> <messageformatversion>2.1</messageformatversion> <messageformatrelease>2.0</messageformatrelease> <messagenumb>56530_PRD</messagenumb> <messagesenderidentifier>MPIGALT</messagesenderidentifier> <messagereceiveridentifier>GALTJNJPRD</messagereceiveridentifier> <messagedateformat>204</messagedateformat> <messagedate>20100907112552</messagedate> </ichicsrmessageheader>- <safetyreport><safetyreportversion>3</safetyreportversion> <safetyreportid>ES-MPIJNJ-2007_02502</safetyreportid> <primarysourcecountry>ES</primarysourcecountry> <occurcountry>ES</occurcountry> <transmissiondateformat>102</transmissiondateformat> <transmissiondate>20100903</transmissiondate> <reporttype>3</reporttype> <serious>1</serious> <seriousnessdeath>2</seriousnessdeath> <seriousnesslifethreatening>2</seriousnesslifethreatening> <seriousnesshospitalization>2</seriousnesshospitalization> <seriousnessdisabling>2</seriousnessdisabling> <seriousnesscongenitalanomali>2</seriousnesscongenitalanomali> <seriousnessother>1</seriousnessother> <receivedateformat>102</receivedateformat> <receivedate>20070719</receivedate> <receiptdateformat>102</receiptdateformat> <receiptdate>20100903</receiptdate> <additionaldocument>2</additionaldocument> <fulfillexpeditecriteria>1</fulfillexpeditecriteria> <companynumb>eee</companynumb> <duplicate>1</duplicate> <medicallyconfirm>1</medicallyconfirm> - <summary><narrativeincludeclinical>xxxxxxx.</narrativeincludeclinical> <reportercomment /> <senderdiagnosismeddraversion>13.0</senderdiagnosismeddraversion> <senderdiagnosis>10028651</senderdiagnosis> </summary></patient></safetyreport></ichicsr>And the values are fetched from the number of tables with some join condition.Please help me here. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-10-31 : 02:01:44
|
I am more interested in how you expect us to fix the query without prior knownledge to your system.We don't know what your tables are named, what information is stored where and so on.Also, we have no idea of foreign keys, relations and logical storage decisions.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
XML extraction