*
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 ??