Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Read XML data with related attributes/properites Expand / Collapse
Author
Message
Posted Wednesday, December 04, 2013 10:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 06, 2013 2:20 PM
Points: 14, Visits: 72
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>

Post #1519862
Posted Wednesday, December 04, 2013 10:49 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:52 AM
Points: 470, Visits: 823
Share a sample how you want to view the result set. The details u shared is not helping
Post #1519869
Posted Thursday, December 05, 2013 7:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 06, 2013 2:20 PM
Points: 14, Visits: 72
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
&lt;IndustryDome&gt;3951LX01&lt;/IndustryDome&gt;

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.



Post #1520064
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse