SQLTeam.com | Weblogs | Forums

Read XMl file


#1

xml
Statements
Statement
Summary summarybalancecarriedforwards="291.94" summarytransfervalue="-2627.51" summarydebits="0.00" summarycredits="2919.45" totalmiscdebits="0.00" totalwithdrawalreports="0.00" totalindemnityclaims="0.00" totalsamedaypayments="0.00" totalimputreportsreturns="0.00" totalaruddreports="0.00" totalmisccredits="0.00" totalimputreports="1016.00" openbalance="1903.45" licence="217156" /
/Statement
/Statements

how do I get summarybalancecarriedforwards & summarytransfervalue from the xml


#2

Try this

declare @x xml
set @x=N'
<Statements>
  <Statement>
    <Summary summarybalancecarriedforwards="291.94" summarytransfervalue="-2627.51" summarydebits="0.00" summarycredits="2919.45" totalmiscdebits="0.00" totalwithdrawalreports="0.00" totalindemnityclaims="0.00" totalsamedaypayments="0.00" totalimputreportsreturns="0.00" totalaruddreports="0.00" totalmisccredits="0.00" totalimputreports="1016.00" openbalance="1903.45" licence="217156" />
  </Statement>
</Statements>'

select  @x.value('(/Statements/Statement/Summary/@summarybalancecarriedforwards)[1]', 'decimal(6,2)' ) assummarybalancecarriedforwards,
 @x.value('(/Statements/Statement/Summary/@summarytransfervalue)[1]', 'decimal(6,2)' ) as summarytransfervalue