• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • Last Modified:

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

0
JonMny
Asked:
JonMny
  • 3
1 Solution
 
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

0
 
JonMnyAuthor Commented:
I am not getting <AgingIneligible>

 
0
 
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

0
 
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now