Author |
Topic |
savibp1
Starting Member
6 Posts |
Posted - 2013-03-19 : 06:44:25
|
I need a single query to retrieve all xml attributes .Pls help???edit: moved to proper forum |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-19 : 07:37:57
|
post the structure of that XML variable and also expected output......--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
savibp1
Starting Member
6 Posts |
Posted - 2013-03-19 : 11:55:19
|
quote: Originally posted by savibp1 I need a single query to retrieve all xml attributes .Pls help???
<?xml version="1.0" ?> - <PA_TRANSACTIONS_Test1> <TransID>DX7430701S</TransID> <TransEffDate>2012-11-01T20:00:00</TransEffDate> <TransType>Submission</TransType> <CreateDateTime>2013-03-01T07:27:19.500</CreateDateTime> <CreateOperator>Unnati</CreateOperator> <ObjClass>Aegis-Ins-UW-PolicyAdmin-Work-Application</ObjClass> <UpdateDateTime>2013-03-01T07:32:20.443</UpdateDateTime> <UpdateOperator>Unnati</UpdateOperator> - <TransXml>- <Submission> <ApplicantEntryComplete>false</ApplicantEntryComplete> <AssistantUnderwriter>davisg</AssistantUnderwriter> <CATTIV_USD>0</CATTIV_USD> <pyWorkIDPrefix>A-</pyWorkIDPrefix> <pyWorkPartiesRule>Default</pyWorkPartiesRule> - <Brokerage> <AddressLine1 Special="Red">5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <Assets>0</Assets> <BusinessDesc>Broker/Consultant</BusinessDesc> <StateCode>GA</StateCode> <ZipCode>30342-1365</ZipCode> <numberOfEmployees>0</numberOfEmployees> - <Contact> <AddressLine1>5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <BusinessUnitId>070935</BusinessUnitId> <ZipCode>30342-1365</ZipCode> <pxCreateDateTime>2013-03-01T12:29:24.021Z</pxCreateDateTime> <pxCreateOperator>VimalrajM</pxCreateOperator> </Contact>- <Contact> <AddressLine1>5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <BusinessUnitId>0709352</BusinessUnitId> <ZipCode>30342-1365</ZipCode> <pxCreateDateTime>2013-03-01T12:29:24.021Z</pxCreateDateTime> <pxCreateOperator>SelviM</pxCreateOperator> </Contact> </Brokerage>- <Policy> <SLADate>2013-05-24</SLADate> <SLADatePol>2013-03-06</SLADatePol> <SLADateRen>2013-03-06</SLADateRen> </Policy>- <Submission> <CompletionStatus>Complete</CompletionStatus> <PlacementType>W</PlacementType> <ProductClass>Excess</ProductClass> <SubmissionID>DX7430701S</SubmissionID> <SubmissionStatus>Converted</SubmissionStatus> </Submission>- <Quote> <DSSUBNO>72999</DSSUBNO> <SLADate>2013-03-06</SLADate> <SLADateRen>2013-03-06</SLADateRen> <SurplusCredit>0</SurplusCredit> <InsuredCompany /> <Submission /> </Quote>- <InsuredCompany> <AddressLine1>1700 Pacific Ave</AddressLine1> <AddressLine2>Ste 2900</AddressLine2> <Assets>492000000</Assets> <BusinessDesc>Gas (Local Dist/Intg Co)</BusinessDesc> <ZipCode>75201-4666</ZipCode> <numberOfEmployees>0</numberOfEmployees> - <Contact> <AddressLine1>1700 Pacific Ave</AddressLine1> <AddressLine2>Ste 2900</AddressLine2> <Title>Chief Accounting Officer</Title> <ZipCode>75201-4666</ZipCode> </Contact> </InsuredCompany> </Submission> </TransXml> </PA_TRANSACTIONS_Test1> |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-20 : 10:07:52
|
So what should be the output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-03-20 : 15:01:56
|
There are no attributes in the XML above, only entities. N 56°04'39.26"E 12°55'05.63" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-03-20 : 16:18:18
|
Try thisDECLARE @Data XML = N'<?xml version="1.0" ?> <PA_TRANSACTIONS_Test1> <TransID>DX7430701S</TransID> <TransEffDate>2012-11-01T20:00:00</TransEffDate> <TransType>Submission</TransType> <CreateDateTime>2013-03-01T07:27:19.500</CreateDateTime> <CreateOperator>Unnati</CreateOperator> <ObjClass>Aegis-Ins-UW-PolicyAdmin-Work-Application</ObjClass> <UpdateDateTime>2013-03-01T07:32:20.443</UpdateDateTime> <UpdateOperator>Unnati</UpdateOperator> <TransXml> <Submission> <ApplicantEntryComplete>false</ApplicantEntryComplete> <AssistantUnderwriter>davisg</AssistantUnderwriter> <CATTIV_USD>0</CATTIV_USD> <pyWorkIDPrefix>A-</pyWorkIDPrefix> <pyWorkPartiesRule>Default</pyWorkPartiesRule> <Brokerage> <AddressLine1 Special="Red">5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <Assets>0</Assets> <BusinessDesc>Broker/Consultant</BusinessDesc> <StateCode>GA</StateCode> <ZipCode>30342-1365</ZipCode> <numberOfEmployees>0</numberOfEmployees> <Contact> <AddressLine1>5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <BusinessUnitId>070935</BusinessUnitId> <ZipCode>30342-1365</ZipCode> <pxCreateDateTime>2013-03-01T12:29:24.021Z</pxCreateDateTime> <pxCreateOperator>VimalrajM</pxCreateOperator> </Contact> <Contact> <AddressLine1>5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <BusinessUnitId>0709352</BusinessUnitId> <ZipCode>30342-1365</ZipCode> <pxCreateDateTime>2013-03-01T12:29:24.021Z</pxCreateDateTime> <pxCreateOperator>SelviM</pxCreateOperator> </Contact> </Brokerage> <Policy> <SLADate>2013-05-24</SLADate> <SLADatePol>2013-03-06</SLADatePol> <SLADateRen>2013-03-06</SLADateRen> </Policy> <Submission> <CompletionStatus>Complete</CompletionStatus> <PlacementType>W</PlacementType> <ProductClass>Excess</ProductClass> <SubmissionID>DX7430701S</SubmissionID> <SubmissionStatus>Converted</SubmissionStatus> </Submission> <Quote> <DSSUBNO>72999</DSSUBNO> <SLADate>2013-03-06</SLADate> <SLADateRen>2013-03-06</SLADateRen> <SurplusCredit>0</SurplusCredit> <InsuredCompany /> <Submission /> </Quote> <InsuredCompany> <AddressLine1>1700 Pacific Ave</AddressLine1> <AddressLine2>Ste 2900</AddressLine2> <Assets>492000000</Assets> <BusinessDesc>Gas (Local Dist/Intg Co)</BusinessDesc> <ZipCode>75201-4666</ZipCode> <numberOfEmployees>0</numberOfEmployees> <Contact> <AddressLine1>1700 Pacific Ave</AddressLine1> <AddressLine2>Ste 2900</AddressLine2> <Title>Chief Accounting Officer</Title> <ZipCode>75201-4666</ZipCode> </Contact> </InsuredCompany> </Submission> </TransXml> </PA_TRANSACTIONS_Test1>';SELECT d.n.value('(local-name(.))', 'VARCHAR(MAX)') AS Entity, d.n.value('(.)', 'VARCHAR(MAX)') AS ValueFROM @Data.nodes('(//*[count(*) = 0])') AS d(n); N 56°04'39.26"E 12°55'05.63" |
|
|
savibp1
Starting Member
6 Posts |
Posted - 2013-03-22 : 01:22:34
|
quote: Originally posted by SwePeso Try thisDECLARE @Data XML = N'<?xml version="1.0" ?> <PA_TRANSACTIONS_Test1> <TransID>DX7430701S</TransID> <TransEffDate>2012-11-01T20:00:00</TransEffDate> <TransType>Submission</TransType> <CreateDateTime>2013-03-01T07:27:19.500</CreateDateTime> <CreateOperator>Unnati</CreateOperator> <ObjClass>Aegis-Ins-UW-PolicyAdmin-Work-Application</ObjClass> <UpdateDateTime>2013-03-01T07:32:20.443</UpdateDateTime> <UpdateOperator>Unnati</UpdateOperator> <TransXml> <Submission> <ApplicantEntryComplete>false</ApplicantEntryComplete> <AssistantUnderwriter>davisg</AssistantUnderwriter> <CATTIV_USD>0</CATTIV_USD> <pyWorkIDPrefix>A-</pyWorkIDPrefix> <pyWorkPartiesRule>Default</pyWorkPartiesRule> <Brokerage> <AddressLine1 Special="Red">5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <Assets>0</Assets> <BusinessDesc>Broker/Consultant</BusinessDesc> <StateCode>GA</StateCode> <ZipCode>30342-1365</ZipCode> <numberOfEmployees>0</numberOfEmployees> <Contact> <AddressLine1>5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <BusinessUnitId>070935</BusinessUnitId> <ZipCode>30342-1365</ZipCode> <pxCreateDateTime>2013-03-01T12:29:24.021Z</pxCreateDateTime> <pxCreateOperator>VimalrajM</pxCreateOperator> </Contact> <Contact> <AddressLine1>5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <BusinessUnitId>0709352</BusinessUnitId> <ZipCode>30342-1365</ZipCode> <pxCreateDateTime>2013-03-01T12:29:24.021Z</pxCreateDateTime> <pxCreateOperator>SelviM</pxCreateOperator> </Contact> </Brokerage> <Policy> <SLADate>2013-05-24</SLADate> <SLADatePol>2013-03-06</SLADatePol> <SLADateRen>2013-03-06</SLADateRen> </Policy> <Submission> <CompletionStatus>Complete</CompletionStatus> <PlacementType>W</PlacementType> <ProductClass>Excess</ProductClass> <SubmissionID>DX7430701S</SubmissionID> <SubmissionStatus>Converted</SubmissionStatus> </Submission> <Quote> <DSSUBNO>72999</DSSUBNO> <SLADate>2013-03-06</SLADate> <SLADateRen>2013-03-06</SLADateRen> <SurplusCredit>0</SurplusCredit> <InsuredCompany /> <Submission /> </Quote> <InsuredCompany> <AddressLine1>1700 Pacific Ave</AddressLine1> <AddressLine2>Ste 2900</AddressLine2> <Assets>492000000</Assets> <BusinessDesc>Gas (Local Dist/Intg Co)</BusinessDesc> <ZipCode>75201-4666</ZipCode> <numberOfEmployees>0</numberOfEmployees> <Contact> <AddressLine1>1700 Pacific Ave</AddressLine1> <AddressLine2>Ste 2900</AddressLine2> <Title>Chief Accounting Officer</Title> <ZipCode>75201-4666</ZipCode> </Contact> </InsuredCompany> </Submission> </TransXml> </PA_TRANSACTIONS_Test1>';SELECT d.n.value('(local-name(.))', 'VARCHAR(MAX)') AS Entity, d.n.value('(.)', 'VARCHAR(MAX)') AS ValueFROM @Data.nodes('(//*[count(*) = 0])') AS d(n); N 56°04'39.26"E 12°55'05.63"
|
|
|
savibp1
Starting Member
6 Posts |
Posted - 2013-03-22 : 01:23:50
|
Thanks for your solution.this is very useful for me.[quote]Originally posted by SwePeso Try thisDECLARE @Data XML = N'<?xml version="1.0" ?> <PA_TRANSACTIONS_Test1> <TransID>DX7430701S</TransID> <TransEffDate>2012-11-01T20:00:00</TransEffDate> <TransType>Submission</TransType> <CreateDateTime>2013-03-01T07:27:19.500</CreateDateTime> <CreateOperator>Unnati</CreateOperator> <ObjClass>Aegis-Ins-UW-PolicyAdmin-Work-Application</ObjClass> <UpdateDateTime>2013-03-01T07:32:20.443</UpdateDateTime> <UpdateOperator>Unnati</UpdateOperator> <TransXml> <Submission> <ApplicantEntryComplete>false</ApplicantEntryComplete> <AssistantUnderwriter>davisg</AssistantUnderwriter> <CATTIV_USD>0</CATTIV_USD> <pyWorkIDPrefix>A-</pyWorkIDPrefix> <pyWorkPartiesRule>Default</pyWorkPartiesRule> <Brokerage> <AddressLine1 Special="Red">5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <Assets>0</Assets> <BusinessDesc>Broker/Consultant</BusinessDesc> <StateCode>GA</StateCode> <ZipCode>30342-1365</ZipCode> <numberOfEmployees>0</numberOfEmployees> <Contact> <AddressLine1>5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <BusinessUnitId>070935</BusinessUnitId> <ZipCode>30342-1365</ZipCode> <pxCreateDateTime>2013-03-01T12:29:24.021Z</pxCreateDateTime> <pxCreateOperator>VimalrajM</pxCreateOperator> </Contact> <Contact> <AddressLine1>5605 Glenridge Dr NE</AddressLine1> <AddressLine2>One Premier Plaza, Ste 300</AddressLine2> <BusinessUnitId>0709352</BusinessUnitId> <ZipCode>30342-1365</ZipCode> <pxCreateDateTime>2013-03-01T12:29:24.021Z</pxCreateDateTime> <pxCreateOperator>SelviM</pxCreateOperator> </Contact> </Brokerage> <Policy> <SLADate>2013-05-24</SLADate> <SLADatePol>2013-03-06</SLADatePol> <SLADateRen>2013-03-06</SLADateRen> </Policy> <Submission> <CompletionStatus>Complete</CompletionStatus> <PlacementType>W</PlacementType> <ProductClass>Excess</ProductClass> <SubmissionID>DX7430701S</SubmissionID> <SubmissionStatus>Converted</SubmissionStatus> </Submission> <Quote> <DSSUBNO>72999</DSSUBNO> <SLADate>2013-03-06</SLADate> <SLADateRen>2013-03-06</SLADateRen> <SurplusCredit>0</SurplusCredit> <InsuredCompany /> <Submission /> </Quote> <InsuredCompany> <AddressLine1>1700 Pacific Ave</AddressLine1> <AddressLine2>Ste 2900</AddressLine2> <Assets>492000000</Assets> <BusinessDesc>Gas (Local Dist/Intg Co)</BusinessDesc> <ZipCode>75201-4666</ZipCode> <numberOfEmployees>0</numberOfEmployees> <Contact> <AddressLine1>1700 Pacific Ave</AddressLine1> <AddressLine2>Ste 2900</AddressLine2> <Title>Chief Accounting Officer</Title> <ZipCode>75201-4666</ZipCode> </Contact> </InsuredCompany> </Submission> </TransXml> </PA_TRANSACTIONS_Test1>';SELECT d.n.value('(local-name(.))', 'VARCHAR(MAX)') AS Entity, d.n.value('(.)', 'VARCHAR(MAX)') AS ValueFROM @Data.nodes('(//*[count(*) = 0])') AS d(n); N 56°04'39.26"E 12°55'05.63"[/quote] |
|
|
|