November 18, 2010 at 8:52 pm
Dear all,
I am very new to XML programming and i have stuck with the format which i need to generate.Not very sure we can generate the same format in T-Sql itself or any other options.
My format should look like this:
<? 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 > prd_88 </ messagenumb >
< messagesenderidentifier >A </ messagesenderidentifier >
< messagereceiveridentifier >XYZ </ messagereceiveridentifier >
< messagedateformat > 204 </ messagedateformat >
< messagedate > 20100907112552 </ messagedate >
</ ichicsrmessageheader >
- < safetyreport >
< safetyreportversion > 3 </ safetyreportversion >
< safetyreportid > iio90-2007_02502 </ safetyreportid >
< primarysourcecountry > in</ primarysourcecountry >
< occurcountry > op</ 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 > ESI PF </ companynumb >
< duplicate > 1 </ duplicate >
< medicallyconfirm > 1 </ medicallyconfirm >
- < reportduplicate >
< duplicatenumb > 915230244 </ duplicatenumb >
</ reportduplicate >
- < primarysource >
< reporterorganization > Health Authorities </ reporterorganization >
< reportercountry > oi</ reportercountry >
< qualification > 1 </ qualification >
</ primarysource >
- < sender >
< sendertype > 1 </ sendertype >
< senderorganization >v </ senderorganization >
< senderdepartment >q </ senderdepartment >
< sendergivename > Veronique </ sendergivename >
< sendermiddlename > F. </ sendermiddlename >
< senderfamilyname > MD.SHSH </ senderfamilyname >
< senderstreetaddress > 40 Landsdowne Street </ senderstreetaddress >
< sendercity > canara</ sendercity >
< senderstate > MA </ senderstate >
< senderpostcode > 02139 </ senderpostcode >
< sendercountrycode > IP</ sendercountrycode >
< sendertel > 6175512972 </ sendertel >
< sendertelcountrycode > 01 </ sendertelcountrycode >
< senderemailaddress > Vf@xyz.com </ senderemailaddress >
</ sender >
< patientinitial > PRIVACY </ patientinitial >
- < medicalhistoryepisode >
< patientepisodenamemeddraversion > 13.0 </ patientepisodenamemeddraversion >
< patientepisodename > 10028228 </ patientepisodename >
</ medicalhistoryepisode >
- < reaction >
< primarysourcereaction > my</ primarysourcereaction >
< reactionmeddraversionllt > 13.0 </ reactionmeddraversionllt >
< reactionmeddrallt > 10028651 </ reactionmeddrallt >
< reactionmeddraversionpt > 13.0 </ reactionmeddraversionpt >
< reactionmeddrapt > my</ reactionmeddrapt >
< termhighlighted > 3 </ termhighlighted >
< reactionstartdateformat > 102 </ reactionstartdateformat >
< reactionstartdate > 20070121 </ reactionstartdate >
< reactionfirsttime > 38 </ reactionfirsttime >
< reactionfirsttimeunit > 804 </ reactionfirsttimeunit >
< reactionoutcome > 3 </ reactionoutcome >
</ reaction >
- < drug >
< drugcharacterization > 1 </ drugcharacterization >
< medicinalproduct > 123</ medicinalproduct >
< drugbatchnumb > not reported </ drugbatchnumb >
< drugauthorizationholder > XYZ </ drugauthorizationholder >
< drugadministrationroute > 042 </ drugadministrationroute >
< drugindicationmeddraversion > 13.0 </ drugindicationmeddraversion >
< drugindication > 10028228 </ drugindication >
< drugstartdateformat > 102 </ drugstartdateformat >
< drugstartdate > 20061215 </ drugstartdate >
< drugstartperiod > 38 </ drugstartperiod >
< drugstartperiodunit > 804 </ drugstartperiodunit >
< drugenddateformat > 102 </ drugenddateformat >
< drugenddate > 20070220 </ drugenddate >
< drugtreatmentduration > 68 </ drugtreatmentduration >
< drugtreatmentdurationunit > 804 </ drugtreatmentdurationunit >
< actiondrug > 1 </ actiondrug >
< drugrecurreadministration > 3 </ drugrecurreadministration >
- < activesubstance >
< activesubstancename > BORTEZOMIB </ activesubstancename >
</ activesubstance >
- < drugreactionrelatedness >
< drugreactionassesmeddraversion > 13.0 </ drugreactionassesmeddraversion >
< drugreactionasses > 10028651 </ drugreactionasses >
< drugassessmentsource > xyzY </ drugassessmentsource >
< drugresult > Possible </ drugresult >
</ drugreactionrelatedness >
- < drugreactionrelatedness >
< drugreactionassesmeddraversion > 13.0 </ drugreactionassesmeddraversion >
< drugreactionasses > 10028651 </ drugreactionasses >
< drugassessmentsource > PRIMARY SOURCE REPORTER </ drugassessmentsource >
< drugresult > Not Reported </ drugresult >
</ drugreactionrelatedness >
</ drug >
- < summary >
< narrativeincludeclinical > pataaaa. </ narrativeincludeclinical >
< reportercomment />
< senderdiagnosismeddraversion > 13.0 </ senderdiagnosismeddraversion >
< senderdiagnosis > 10028651 </ senderdiagnosis >
</ summary >
</ patient >
</ safetyreport >
</ ichicsr >
Any idea how we can generate with T-SQL,any reference or any suggestions or any query to help.
And the fields are fetching from multiple database tables by joining if you required to get into one temp table also i can make it into one temp table.
So i need to get the similar format which i showed in the above.
Please help me
Thanks,
Gangadhar
November 18, 2010 at 9:47 pm
What version of SQL Server are you running? Also, it would help if you provided the DDL for the tables, sample data for the tables, and the expected results based on the sample data.
Please read the first article I reference below in my signature block regarding asking for help. We are volunteers, so the more information you provide, the better we can help you.
November 21, 2010 at 7:48 pm
Dear Experts,
I am using this table and script to generate the xml output.
CREATE TABLE #tmp
(
messagetypeVARCHAR(50),
messageformatversionDECIMAL(5,1),
messageformatreleaseDECIMAL(5,1),
messagenumbVARCHAR(10),
messagesenderidentifierVARCHAR(50),
messagereceiveridentifierVARCHAR(50),
messagedateformatVARCHAR(50),
messagedateVARCHAR(50),
safetyreportversionINT,
safetyreportidVARCHAR(50),
sentdate varchar(10)
--...
)
GO
INSERT INTO #tmp
VALUES
(
'ichicsr',
'2.1',
'2',
'prd_88',
'A',
'XYZ',
'204',
'20100907112552',
'3',
'iio90-2007_02502',
'wrwrwr'
)
GO
here is the script to get xml.
SELECT
'en' AS "@lang",
messagetype AS "ichicsrmessageheader/messagetype",
messageformatversion AS "ichicsrmessageheader/messageformatversion",
messageformatrelease AS "ichicsrmessageheader/messageformatrelease",
messagenumb AS "ichicsrmessageheader/messagenumb",
messagesenderidentifier AS "ichicsrmessageheader/messagesenderidentifier",
messagereceiveridentifier AS "ichicsrmessageheader/messagereceiveridentifier",
messagedateformat AS "ichicsrmessageheader/messagedateformat",
messagedate AS "ichicsrmessageheader/messagedate",
safetyreportversion AS "ichicsrmessageheader/safetyreportversion",
safetyreportid AS "safetyreport/safetyreportid",
sentdate AS "sentdate/sentdate"
FROM #tmp
FOR XML PATH('ichicsr')
Script was given by wBob.
Here my concern is to have sentdate should be the child node to <safetyreport> node.
I mean to say that if i minimize the <sentdate> node it should come under
<safetyreport> node not as a separate node.
Thanks,
Gangadhar
November 21, 2010 at 8:40 pm
The results of your query give:
<ichicsr lang="en">
<ichicsrmessageheader>
<messagetype>ichicsr</messagetype>
<messageformatversion>2.1</messageformatversion>
<messageformatrelease>2.0</messageformatrelease>
<messagenumb>prd_88</messagenumb>
<messagesenderidentifier>A</messagesenderidentifier>
<messagereceiveridentifier>XYZ</messagereceiveridentifier>
<messagedateformat>204</messagedateformat>
<messagedate>20100907112552</messagedate>
<safetyreportversion>3</safetyreportversion>
</ichicsrmessageheader>
<safetyreport>
<safetyreportid>iio90-2007_02502</safetyreportid>
</safetyreport>
<sentdate>
<sentdate>wrwrwr</sentdate>
</sentdate>
</ichicsr>
Is this what you want?
<ichicsr lang="en">
<ichicsrmessageheader>
<messagetype>ichicsr</messagetype>
<messageformatversion>2.1</messageformatversion>
<messageformatrelease>2.0</messageformatrelease>
<messagenumb>prd_88</messagenumb>
<messagesenderidentifier>A</messagesenderidentifier>
<messagereceiveridentifier>XYZ</messagereceiveridentifier>
<messagedateformat>204</messagedateformat>
<messagedate>20100907112552</messagedate>
<safetyreportversion>3</safetyreportversion>
</ichicsrmessageheader>
<safetyreport>
<safetyreportid>iio90-2007_02502</safetyreportid>
<sentdate>wrwrwr</sentdate>
</safetyreport>
</ichicsr>
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 21, 2010 at 9:04 pm
yes exactly thats what i needed.!!
But i should be able to add multiple child nodes it.If could see my original post i have multiple child node and parent node are there.
I could able to generate ultimately in that format.
Please help me.
Thanks
Gangadhar
November 22, 2010 at 1:29 am
If you want to get the results that WayneS showed you, you can use the fallowing query:
SELECT
'en' AS "@lang",
messagetype AS "ichicsrmessageheader/messagetype",
messageformatversion AS "ichicsrmessageheader/messageformatversion",
messageformatrelease AS "ichicsrmessageheader/messageformatrelease",
messagenumb AS "ichicsrmessageheader/messagenumb",
messagesenderidentifier AS "ichicsrmessageheader/messagesenderidentifier",
messagereceiveridentifier AS "ichicsrmessageheader/messagereceiveridentifier",
messagedateformat AS "ichicsrmessageheader/messagedateformat",
messagedate AS "ichicsrmessageheader/messagedate",
safetyreportversion AS "ichicsrmessageheader/safetyreportversion",
safetyreportid AS "safetyreport/safetyreportid",
sentdate AS "safetyreport/sentdate"
FROM #tmp
FOR XML PATH('ichicsr')
Notice that I’ve just added the level safetyreport to the columns safetyreportid and sentdate.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2010 at 2:13 am
hi
Thanks for the reply. My concern here is to when i ope the xml file in IE web browser if i minimize the <safetyreport> tag the <sentdate> also should get minimized.
I mean to say that <sentdate> should be child node to <safetyreport>.
Like this <sentdate> i can have multiple child node to <safetyreport>
chicsr--> main parent node
ichicsrmessageheader --> Child node to chicsr
safetyreport --> child node to chicsr
sentdate --> child node to safetyreport
receive date --> child node to safetyreport (i will add it later)
rejecteddate --> child node to safetyreport (i will add it later)
Hope i am it clear, pls help me to get xml in this format
Thanks,
Gangadhar
November 22, 2010 at 4:19 am
The query that you got does exactly what you asked for. The element sentdate is the child of saftyreport and when you minimize the element saftyreport, you don’t see the element sentdate. If you want to add more children elements for saftyreport, you can do it using the same way that I did with the element sentdate.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2010 at 5:18 am
Hi Adi,
I used the same query and get the result and pasted in notepad and saved file as .xml,when i opened with the IE i could see <safetyreport> as child node and <sentdate> as another child node but not a child of <safetyreport>.
I need <sentdate>as child node to <safetyreport> and when i minimize the <safetyreport> i could not see <sentdate>
Thanks,
Gangadhar
November 22, 2010 at 6:06 am
Looking at what you are trying to accomplish, I see two things wrong.
One, you only provided one record of sample data. It would help if you provide between 5 to 10 records that was representative of the the problem you are trying to solve.
Two, you are selecting the data from a single table. I may be wrong here, but that may be part of your issue as the data is all at the same level.
November 22, 2010 at 6:20 am
Maybe I don’t understand what you need. Maybe you don’t use the correct query. Maybe you don’t open the correct fie. In any case in the output of the query that I wrote safetyreportid and sentdate are both child elements of safetyreport.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2010 at 9:08 am
Okay, do you want this:
<ichicsr lang="en">
<ichicsrmessageheader>
<messagetype>ichicsr</messagetype>
<messageformatversion>2.1</messageformatversion>
<messageformatrelease>2.0</messageformatrelease>
<messagenumb>prd_88</messagenumb>
<messagesenderidentifier>A</messagesenderidentifier>
<messagereceiveridentifier>XYZ</messagereceiveridentifier>
<messagedateformat>204</messagedateformat>
<messagedate>20100907112552</messagedate>
<safetyreportversion>3</safetyreportversion>
</ichicsrmessageheader>
<safetyreport>
<safetyreportid>iio90-2007_02502</safetyreportid>
<sentdate>
<sentdate>wrwrwr</sentdate>
</sentdate>
</safetyreport>
</ichicsr>
or this:
<ichicsr lang="en">
<ichicsrmessageheader>
<messagetype>ichicsr</messagetype>
<messageformatversion>2.1</messageformatversion>
<messageformatrelease>2.0</messageformatrelease>
<messagenumb>prd_88</messagenumb>
<messagesenderidentifier>A</messagesenderidentifier>
<messagereceiveridentifier>XYZ</messagereceiveridentifier>
<messagedateformat>204</messagedateformat>
<messagedate>20100907112552</messagedate>
<safetyreportversion>3</safetyreportversion>
</ichicsrmessageheader>
<safetyreport>
<safetyreportid>iio90-2007_02502</safetyreportid>
<sentdate>wrwrwr</sentdate>
</safetyreport>
</ichicsr>
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 23, 2010 at 2:31 am
Hi,
1.The example data which i have given for only one record you may be consider for multiple records similar to that only if you want.there is no issue with data.
2.Yes i am getting records from multiple table but i can load that data to temp table or staging table before extracting to XML format.
But i am struggling with the format
<sentdate> should be child node <safetyreport>
Hope you are understood with my issue.
If i open the result which i got int he first query will not match to the requirement.
Please help me or do we have any other methods to get in that format.
Thanks,
Gangadhar
November 23, 2010 at 6:59 am
I am just trying to be sure I understand you correctly based on your last post. You've provided one record of sample data and if that isn't enough make our own?
This is your problem and if you want help you need to be willing to provide that which is requested. You know your problem domain and should be able to provide a sample set that is representative of the problem we are trying to help you solve. If you aren't willing to activily participate in this activity, tell me why we should help?
I've been looking at it but I need additional sample data to do anything more than others have so far. With additional data and what the results should look like based on that data I'm sure that someone will be able to help you solve this problem. It just depends if you are are willing to help us help you.
November 26, 2010 at 2:54 am
This is what you want?
create table computerCA (computerCAID int primary key, computerCAName varchar(300), computerCAEmail varchar(500))
insert computerCA
select 1, 'Laura', 'mymail@hotmail.com' union all
select 2, 'Joe', 'herman@hotmail.com'
SELECT computerCAID , computerCAName, computerCAEmail FROM COMPUTERCA for xml raw ('ichicsrmessageheader'),elements --ROOT('ichicsrmessageheader')
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply