asked on

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

BrandonGalderisi

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

JonMny

ASKER

I am not getting <AgingIneligible>

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrandonGalderisi

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.