December 22, 2016 at 9:59 am
Hi
I am trying to flatten an XML field where each "source" would be listed no matter if there was an "amount" or not.
So below is the xml data and how I would like it displayed.
I tried using and index but since the XML display a source for each field it displays the wrong source for the amount
XML Data:
<Data>
<Insurance>Yes</Insurance>
<_x0031_._x0020_ealth>Yes</_x0031_._x0020_ealth>
<Income_x0020_sources>Yes</Income_x0020_sources>
<Non_x0020_Cash_x0020_Benefits>Yes</Non_x0020_Cash_x0020_Benefits>
<Monthly_x0020_Income>
<Monthly_x0020_Income_x0020_ST>
<Source>Earned Income</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Unemployment Insurance</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Supplemental Security Income</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Social Security Disability Income</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>VA Service-Connected DIsability Compensation</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>VA Non-Service Connected Disability Pension</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Private Disability Insurance</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Worker's Compensation</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Temporary Assistance for Needy Families (TANF)</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>General Assistance (GA)</Source>
<Amount>303</Amount>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Retirement Income from Social Security</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Pension or retirement income from a former job</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Child Support</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Alimony or other Spousal Support</Source>
</Monthly_x0020_Income_x0020_ST>
</Monthly_x0020_Income>
I used :
Data.value('(./Data/Monthly_x0020_Income/Monthly_x0020_Income_x0020_ST/Source)[1]','varchar(50)') As [[Earned_Income]
,Data.value('(./Data/Monthly_x0020_Income/Monthly_x0020_Income_x0020_ST/Amount)[1]','varchar(50)') As [Earned_Income_Amount]
Output looks like Earned Income, 303
which is wrong since the 303 is General Assistance (GA)
My desired output is
Earned Income , 0 , Unemployment Insurance, 0,etc.... ,General Assistance (GA),303, etc....
Hopefully I wasn't too confusing!!
Thanks
December 22, 2016 at 11:00 am
Hi
So with a little thought, I decided to try to get data a level up using
, Data.value('(./Data/Monthly_x0020_Income/Monthly_x0020_Income_x0020_ST)[1]','varchar(50)') As [Earned_Income]
My output shows EarnedIncome301, GA, abc101, etc....
wondering if I can parse those fields so it looks like earnedincome 301,ga 0 ,abc 101, etc
Thanks Again
December 22, 2016 at 3:02 pm
You need to look at the nodes() syntax for parsing XML. Something like:
declare @x xml
set @x='<Data>
<Insurance>Yes</Insurance>
<_x0031_._x0020_ealth>Yes</_x0031_._x0020_ealth>
<Income_x0020_sources>Yes</Income_x0020_sources>
<Non_x0020_Cash_x0020_Benefits>Yes</Non_x0020_Cash_x0020_Benefits>
<Monthly_x0020_Income>
<Monthly_x0020_Income_x0020_ST>
<Source>Earned Income</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Unemployment Insurance</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Supplemental Security Income</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Social Security Disability Income</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>VA Service-Connected DIsability Compensation</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>VA Non-Service Connected Disability Pension</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Private Disability Insurance</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Worker''s Compensation</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Temporary Assistance for Needy Families (TANF)</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>General Assistance (GA)</Source>
<Amount>303</Amount>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Retirement Income from Social Security</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Pension or retirement income from a former job</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Child Support</Source>
</Monthly_x0020_Income_x0020_ST>
<Monthly_x0020_Income_x0020_ST>
<Source>Alimony or other Spousal Support</Source>
</Monthly_x0020_Income_x0020_ST>
</Monthly_x0020_Income>
</Data>'
select inc.value('(Source)[1]','varchar(100)') incSource,
inc.value('(Amount)[1]','varchar(100)') incAmount
from
@x.nodes('//Monthly_x0020_Income_x0020_ST') d(inc)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply