Read XML data with related attributes/properites

  • 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>

  • Share a sample how you want to view the result set. The details u shared is not helping

  • 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