Tech
Forums
Jobs
Books
Events
Live
Interviews
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
2
Reply
Get the columns from the XML data
rahul ahuja
Oct 6 2014 11:47 AM
711
Reply
I have table with following columns
tblProductProperties
(ID {Bigint},
Name {Varchar},
AccountID {long},
FileDate {DateTime},
RawXMLData {xml}
)
Now raw xml is contains data as follows
<R01>
<RAW_CODE />
<CORRESPONDENCE_ID>Some Int values</CORRESPONDENCE_ID>
<FIRST_NAME>Name1</FIRST_NAME>
<LAST_NAME>Name2</LAST_NAME>
<COUNTY />
<EMAIL>
[email protected]
</EMAIL>
<ACCTID>Some Int value </ACCTID>
<COLOR>Text value</COLOR>
<WEIGHT>Weight description </WEIGHT>
</R01>
select * tblProductProperties
out put of query looks like as follow
ID Name AccountID FileDate RawXMLData
101 ABC 123133 2014/11/12 "
<R01> <RAW_CODE /> <CORRESPONDENCE_ID>1293</CORRESPONDENCE_ID> <FIRST_NAME>Joan</FIRST_NAME> <LAST_NAME>Wade</LAST_NAME> <COUNTY /> <EMAIL>
[email protected]
</EMAIL> <ACCTID>93</ACCTID> <COLOR>Red </COLOR> <WEIGHT>60GMS</WEIGHT> </R01>
"
102 AVC 120201 2014/11/12 "
<R01> <RAW_CODE /> <CORRESPONDENCE_ID>1292</CORRESPONDENCE_ID> <FIRST_NAME>Name1</FIRST_NAME> <LAST_NAME>Name2</LAST_NAME> <COUNTY /> <EMAIL>
[email protected]
</EMAIL> <ACCTID>92</ACCTID> <COLOR>Blue </COLOR> <WEIGHT>60GMS</WEIGHT> </R01>
"
Now i want to show output as follows
ID Name AccountID FileDate
RAW_CODE
CORRESPONDENCE_ID
FIRST_NAME
LAST_NAME
COUNTY
EMAIL
ACCTID
COLOR
WEIGHT
101 ABC 123133 2014/11/12 NULL 1293 Joan Wade Null
joan2054@ 93 RED 60gms
yahoo.com
102 AVC 120201 2014/11/12 NULL 1292 Name1 Name2 NULL
name1@ 92 Blue 60gms
yahoo.com
Can some one help me to find out how to solve this.
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
2
)
Next Recommended Forum
how to determine available hard disk space where the databas
select statement while loop