Click here to Skip to main content
15,063,889 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Team,
I Am using below xml to retrieve data
XML
declare @exp XML

set @exp=
N'<SpItem>
        <SPId>1</SPId>
        <NName>ab</NName>

        <Expr>
            <Oprt> </Oprt>
            <Nr>10</Nr>
            <Oprt>+</Oprt>
            <Data>
                <Type>Mc</Type>
                <Id>10</Id>
                <Value>Mc Value</Value>
            </Data>
            <Oprt>+</Oprt>
            <Data>
                <Type>DataElement</Type>
                <Id>10</Id>
                <Value>Mc Value</Value>
            </Data>
            <Oprt>*</Oprt>

            <SPItem>
                <SPId></SPId>
                <NName>My SPad</NName>
                <Expr>
                    <Oprt>  </Oprt>
                    <Data>
                        <Type>Metric</Type>
                        <Id>10</Id>
                        <Value>Mc Value</Value>
                    </Data>
                </Expr>
            </SPItem>
        </Expr>
</SpItem>'


and i used below query to retrieve

SQL
select
col.value('(Oprt/text())[1]','varchar(50)'),
col.value('(text())[1]','varchar(50)')
 from @exp.nodes('//Expr') as tab(col)
 cross apply col.nodes('Data') as dtype(dt)


can any one help me to retrieve all the data from xml. Thanks in advance.
Posted
Comments
Umer Akram 27-Jan-15 2:43am
   
what is your desired out ? same share a sample

1 solution

SPId	NName	Oprt	Nr	Type	Inner SPId
1	      ab	        10			
1	      ab	+	    10	    DataElement	
1	      ab	+	    10	    Mc
1	      ab	*	    		
1	      ab	+	    10	    Metric	  2



Here is the expected out put
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900