Another XML ?

Question

Post reply

Another XML ?

jbalbo

SSCertifiable

Points: 7580
More actions
September 25, 2019 at 6:31 pm
Go to Answer
#3683410

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>

DesNorton

SSC-Insane

Points: 24278

September 25, 2019 at 6:54 pm

Answer

#3683422

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);

How to post data/code on a forum to get the best help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

jbalbo SSCertifiable Points: 7580 More actions · Answer 1

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