December 4, 2013 at 10:18 pm
Hi Team , i have a table with one of the column of xml type. the column contains xml like given below. I want to read this xml from the table and show as below with T-sql query
"EmployeeID" "IndustryDome" "description "
where Description value comes from the value of AllDome/ITEM/Dome /Description whose Dome equals to IndustryDome value
EX:
1166586 3951LX01 Description10
Please help.
<GetEmployeeDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<return xmlns="http://applications.apch1.com/webservice/schema/">
<EmployeeID>1166586</EmployeeID>
<BankAccounts>
<BankAccount>123456</BankAccount>
<BankAccount>7890123</BankAccount>
</BankAccounts>
<industries>
<MarketActivity>Recycling</MarketActivity>
<IndustryElectro>1191</IndustryElectro>
<IndustryElectri>33LX40</IndustryElectri>
<IndustryDome>3951LX01</IndustryDome>
<IndustryInter>36.631</IndustryInter>
<AllInter>
<ITEM>
<Inter>28.610</Inter>
<description>Description1</description>
</ITEM>
<ITEM>
<Inter>24.660</Inter>
<description>Description2</description>
</ITEM>
<ITEM>
<Inter>36.631</Inter>
<description>Description3</description>
</ITEM>
<ITEM>
<Inter>36.639</Inter>
<description>Description4</description>
</ITEM>
<ITEM>
<Inter>51.479</Inter>
<description>Description5</description>
</ITEM>
</AllInter>
<AllDome>
<ITEM>
<Dome>3LX90505</Dome>
<description>Description6</description>
</ITEM>
<ITEM>
<Dome>50910100</Dome>
<description>Description7</description>
</ITEM>
<ITEM>
<Dome>28LXLX10</Dome>
<description>Description8</description>
</ITEM>
<ITEM>
<Dome>3421LX01</Dome>
<description>Description9</description>
</ITEM>
<ITEM>
<Dome>3951LX01</Dome>
<description>Description10</description>
</ITEM>
<ITEM>
<Dome>39520000</Dome>
<description>Description11</description>
</ITEM>
</AllDome>
</industries>
</return>
</GetEmployeeDetails>
December 4, 2013 at 10:49 pm
Share a sample how you want to view the result set. The details u shared is not helping
December 5, 2013 at 7:22 am
Hi Twin.devil,
THe out put i am looking is 3 columns (i just separated middle column/value in bold font)
"EmployeeID" "IndustryDome" "description "
1166586 3951LX01 Description10
we need to read 2nd column from
<IndustryDome>3951LX01</IndustryDome>
and the description from
<AllDome>
..
...
<ITEM>
<Dome>3951LX01</Dome>
<description>Description10</description>
</ITEM> whose Dome value = IndustryDome's value
In another scenario ,ex: xml
<EmployeeID>123456</EmployeeID>
...
...
<IndustryElectri>33LX40</IndustryElectri>
<AllElectri>
<ITEM>
<Electri>33LX40</Inter>
<description>Description21</description>
</ITEM>
<Electri>55L940</Inter>
<description>Description22</description>
</ITEM>
<ITEM> </ITEM>
<ITEM> </ITEM>
...
...
</AllElectri>
then the out put is Employee ID , IndustriElectri,Descritpion
= 123456 , 33LX40, Description21
Description21 came from whose Electri value = IndustriElectri.
Its basically reading code value at the beginning, go down search for matching section/value then read the description value.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply