Read XML tsql

I have an xml file trying to read the fields for this xml to update a table

I am having problems reading

<AgingDetail>

<AgingIneligible>

nodes can anyone tell me how to read these values.



<ArrayOfAging>
	<Aging>
<AgingMethod>1</AgingMethod><CrossAge>10</CrossAge><Id>1</Id><LoanItemId>1</LoanItemId><PastDue>100</PastDue>
		<ArrayOfAgingDetail>
			<AgingDetail>
				<AgingText>Test Aging Text-11</AgingText><Id>1</Id><LISAgingId>1</LISAgingId>
			</AgingDetail>
			<AgingDetail>
				<AgingText>Test Aging Text-12</AgingText><Id>2</Id><LISAgingId>1</LISAgingId>
			</AgingDetail>
		</ArrayOfAgingDetail>
		<ArrayOfAgingIneligible>
			<AgingIneligible>
				<Id>1</Id><Ineligible>Ineligible-11</Ineligible><LISAgingId>1</LISAgingId>
			</AgingIneligible>
			<AgingIneligible>											<Id>2</Id><Ineligible>Ineligible-12</Ineligible><LISAgingId>1</LISAgingId>
			</AgingIneligible>
		</ArrayOfAgingIneligible>
	</Aging>
	<Aging>							<AgingMethod>2</AgingMethod><CrossAge>120</CrossAge><Id>2</Id><LoanItemId>1</LoanItemId><PastDue>10</PastDue>
		<ArrayOfAgingDetail>
			<AgingDetail>
				<AgingText>Test Aging Text-21</AgingText><Id>2</Id><LISAgingId>2</LISAgingId>
			</AgingDetail>
			<AgingDetail>
				<AgingText>Test Aging Text-22</AgingText><Id>1</Id><LISAgingId>2</LISAgingId>
			</AgingDetail>
		</ArrayOfAgingDetail>
		<ArrayOfAgingIneligible>
			<AgingIneligible>
				<Id>1</Id><Ineligible>Ineligible-21</Ineligible><LISAgingId>2</LISAgingId>
			</AgingIneligible>
			<AgingIneligible>													<Id>2</Id><Ineligible>Ineligible-22</Ineligible><LISAgingId>2</LISAgingId>
			</AgingIneligible>
		</ArrayOfAgingIneligible>
	</Aging>
</ArrayOfAging>

Open in new window

LVL 9
JonMnyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
How does this look?
declare @x xml
set @x=
N'<ArrayOfAging>
  <Aging>
    <AgingMethod>1</AgingMethod>
    <CrossAge>10</CrossAge>
    <Id>1</Id>
    <LoanItemId>1</LoanItemId>
    <PastDue>100</PastDue>
    <ArrayOfAgingDetail>
      <AgingDetail>
        <AgingText>Test Aging Text-11</AgingText>
        <Id>1</Id>
        <LISAgingId>1</LISAgingId>
      </AgingDetail>
      <AgingDetail>
        <AgingText>Test Aging Text-12</AgingText>
        <Id>2</Id>
        <LISAgingId>1</LISAgingId>
      </AgingDetail>
    </ArrayOfAgingDetail>
    <ArrayOfAgingIneligible>
      <AgingIneligible>
        <Id>1</Id>
        <Ineligible>Ineligible-11</Ineligible>
        <LISAgingId>1</LISAgingId>
      </AgingIneligible>
      <AgingIneligible>
        <Id>2</Id>
        <Ineligible>Ineligible-12</Ineligible>
        <LISAgingId>1</LISAgingId>
      </AgingIneligible>
    </ArrayOfAgingIneligible>
  </Aging>
  <Aging>
    <AgingMethod>2</AgingMethod>
    <CrossAge>120</CrossAge>
    <Id>2</Id>
    <LoanItemId>1</LoanItemId>
    <PastDue>10</PastDue>
    <ArrayOfAgingDetail>
      <AgingDetail>
        <AgingText>Test Aging Text-21</AgingText>
        <Id>2</Id>
        <LISAgingId>2</LISAgingId>
      </AgingDetail>
      <AgingDetail>
        <AgingText>Test Aging Text-22</AgingText>
        <Id>1</Id>
        <LISAgingId>2</LISAgingId>
      </AgingDetail>
    </ArrayOfAgingDetail>
    <ArrayOfAgingIneligible>
      <AgingIneligible>
        <Id>1</Id>
        <Ineligible>Ineligible-21</Ineligible>
        <LISAgingId>2</LISAgingId>
      </AgingIneligible>
      <AgingIneligible>
        <Id>2</Id>
        <Ineligible>Ineligible-22</Ineligible>
        <LISAgingId>2</LISAgingId>
      </AgingIneligible>
    </ArrayOfAgingIneligible>
  </Aging>
</ArrayOfAging>'
select @x
 
declare @i int
exec sp_xml_preparedocument @i=@i output, @x=@x
 
--select * from openxml(@i, '/ArrayOfAging/Aging/ArrayOfAgingIneligible/AgingDetail')
--     with (AgingText_Id  int  './AgingText/Id')
 
 
select * from openxml(@i, '/ArrayOfAging/Aging/ArrayOfAgingDetail/AgingDetail')
     with (AgingDetailText              nvarchar(max)       './AgingText'
          ,AgingDetailId                int                 './Id'
          ,AgingDetailKUSAgingId        int                 './LISAgingId'
          ,PastDue                      int                 '../../PastDue'
          ,LoanItemId                   int                 '../../LoanItemId'
          ,Id                           int                 '../../Id'
          ,CrossAge                     int                 '../../CrossAge'
          ,AgingMethod                  int                 '../../AgingMethod'
          )
 
exec sp_xml_removedocument @i=@i

Open in new window

JonMnyAuthor Commented:
I am not getting <AgingIneligible>

 
BrandonGalderisiCommented:
What about this?

It may not be EXACTLY what you want.  But I am trying to structure this query in a way that you can understand it and adapt it to your specific XML structure.
declare @x xml
set @x=
N'<ArrayOfAging>
  <Aging>
    <AgingMethod>1</AgingMethod>
    <CrossAge>10</CrossAge>
    <Id>1</Id>
    <LoanItemId>1</LoanItemId>
    <PastDue>100</PastDue>
    <ArrayOfAgingDetail>
      <AgingDetail>
        <AgingText>Test Aging Text-11</AgingText>
        <Id>1</Id>
        <LISAgingId>1</LISAgingId>
      </AgingDetail>
      <AgingDetail>
        <AgingText>Test Aging Text-12</AgingText>
        <Id>2</Id>
        <LISAgingId>1</LISAgingId>
      </AgingDetail>
    </ArrayOfAgingDetail>
    <ArrayOfAgingIneligible>
      <AgingIneligible>
        <Id>1</Id>
        <Ineligible>Ineligible-11</Ineligible>
        <LISAgingId>1</LISAgingId>
      </AgingIneligible>
      <AgingIneligible>
        <Id>2</Id>
        <Ineligible>Ineligible-12</Ineligible>
        <LISAgingId>1</LISAgingId>
      </AgingIneligible>
    </ArrayOfAgingIneligible>
  </Aging>
  <Aging>
    <AgingMethod>2</AgingMethod>
    <CrossAge>120</CrossAge>
    <Id>2</Id>
    <LoanItemId>1</LoanItemId>
    <PastDue>10</PastDue>
    <ArrayOfAgingDetail>
      <AgingDetail>
        <AgingText>Test Aging Text-21</AgingText>
        <Id>2</Id>
        <LISAgingId>2</LISAgingId>
      </AgingDetail>
      <AgingDetail>
        <AgingText>Test Aging Text-22</AgingText>
        <Id>1</Id>
        <LISAgingId>2</LISAgingId>
      </AgingDetail>
    </ArrayOfAgingDetail>
    <ArrayOfAgingIneligible>
      <AgingIneligible>
        <Id>1</Id>
        <Ineligible>Ineligible-21</Ineligible>
        <LISAgingId>2</LISAgingId>
      </AgingIneligible>
      <AgingIneligible>
        <Id>2</Id>
        <Ineligible>Ineligible-22</Ineligible>
        <LISAgingId>2</LISAgingId>
      </AgingIneligible>
    </ArrayOfAgingIneligible>
  </Aging>
</ArrayOfAging>'
select @x
 
declare @i int
exec sp_xml_preparedocument @i=@i output, @x=@x
 
--select * from openxml(@i, '/ArrayOfAging/Aging/ArrayOfAgingIneligible/AgingDetail')
--     with (AgingText_Id  int  './AgingText/Id')
 
 
select * from openxml(@i, '/ArrayOfAging/Aging/ArrayOfAgingDetail/AgingDetail')
     with (AgingDetailText              nvarchar(max)       './AgingText'
          ,AgingDetailId                int                 './Id'
          ,AgingDetailKUSAgingId        int                 './LISAgingId'
          ,PastDue                      int                 '../../PastDue'
          ,LoanItemId                   int                 '../../LoanItemId'
          ,Id                           int                 '../../Id'
          ,CrossAge                     int                 '../../CrossAge'
          ,AgingMethod                  int                 '../../AgingMethod'
          )
UNION ALL
select * from openxml(@i, '/ArrayOfAging/Aging/ArrayOfAgingIneligible/AgingIneligible')
     with (Ineligible                   nvarchar(max)       './Ineligible'
          ,AgingDetailId                int                 './Id'
          ,AgingDetailKUSAgingId        int                 './LISAgingId'
          ,PastDue                      int                 '../../PastDue'
          ,LoanItemId                   int                 '../../LoanItemId'
          ,Id                           int                 '../../Id'
          ,CrossAge                     int                 '../../CrossAge'
          ,AgingMethod                  int                 '../../AgingMethod'
          )
 
exec sp_xml_removedocument @i=@i

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrandonGalderisiCommented:
Don't take my last post as a "take this and go away".  I am here to help you if/when you need it.  But when I look at a set of similarly structured XML, I sometimes view things that are similar as the same.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.