Hi
Let me preface this with, I'm sorry I'm so bad with XML.... 🙂
So, What I need to do is if a 'Source' has an 'Amount' I would like to show the Source with the amount , otherwise the Source with Null or 0
The output from below would be
Earned Income Unemployment Insurance Supplemental Security Income Social Security Disability Income etc....
0 0 788 0 ......
Thanks
Joe
<Data>
<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>
<Amount>788</Amount>
</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>
</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>
The following SQL will help you to shred the xml. You can then work on formatting it as you need.
DECLARE @xmlData xml = '<Data>
<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>
<Amount>788</Amount>
</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>
</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
[Source] = D.M.value('(Source/text())[1]', 'varchar(50)')
, Amount = D.M.value('(Amount/text())[1]', 'int')
FROM @xmlData.nodes('Data/Monthly_x0020_Income/Monthly_x0020_Income_x0020_ST') As D(M);
September 25, 2019 at 8:06 pm
Ahh got it!
Works as I need . I wrote the data out to a temp table using a cross apply then chose the values as needed .
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply