XML Parsing in Netezza Failing

538 views Asked by At

*

declare @XML XML=' <Home Folder="8163978" UnitID="1">
- <Site ID="3">
- <VDevice name="TV">
- <FinalCreditResult valid="true" tsv="false" startTimeQuantity="1433919600" startDateTime="06/10/2015 02:00:00" endTimeQuantity="1434006000" endDateTime="06/11/2015 02:00:00" deltaTime="0" fromMatchList="0" startRoundedTimeQuantity="1433919600" startRoundedDateTime="06/10/2015 02:00:00" endRoundedTimeQuantity="1434006000" endRoundedDateTime="06/11/2015 02:00:00" roundedDeltaTime="0">
  <distributionSource sourceId="0" sourceName="Off" cableInsertionIndicator="false" /> 
- <PositiveRanges>
- <Ranges startTime="1433919600" startTimeFormatted="06/10/2015 02:00:00" endTime="1434006000" endTimeFormatted="06/11/2015 02:00:00" offSet="0">
  <CreditDataInfo SourceName="STATIC" /> 
  </Ranges>
  </PositiveRanges>
  </FinalCreditResult>
  <HomeState Name="OffPeriod" startTime="1433919600" startTimeFormatted="06/10/2015 02:00:00" endTime="1434006000" endTimeFormatted="06/11/2015 02:00:00" /> 
  </VDevice>
  </Site>
  </Home>';

select 
                H.value('@Folder', 'varchar(30)') as Folder,
                S.value('@ID', '  varchar(30)') as SU_ID,
                F.value('@startRoundedDateTime', '  varchar(30)') as startRoundedDateTime,
                F.value('@endRoundedDateTime', '  varchar(30)') as endRoundedDateTime,
                R.value('@startTimeFormatted', 'varchar(30)') as startDateTime,
                R.value('@endTimeFormatted', 'varchar(30)') as endDateTime, 
                C.value('@SourceName', 'varchar(30)') as Sourcename
from @XML.nodes('Home') as T(H)
outer apply H.nodes('Site') rt(S)
outer apply S.nodes('VDevice') st(D)
outer apply D.nodes('FinalCreditResult') sr(F)
outer apply F.nodes('PositiveRanges') pt(P)
outer apply P.nodes('Ranges') ht(R)
outer apply R.nodes('CreditDataInfo') pr(C)*

I am using the above query in SQL Server 2012 and I am able to get the data in the form of table.

But when I run the same query in Netezza, it's throwing an error.

Is there any alternative to load the xml data into a netezza table ??

0

There are 0 answers