T SQL : Querying multi - nodes XML

T SQL : Querying multi - nodes XML

-- TEST

DECLARE @Xml XML

DECLARE @Filename varchar(100)

SET @Filename = 'M-PetiksHour-XXX.xml'

SELECT @Xml = CONVERT(XML, BulkColumn)

FROM OPENROWSET(BULK 'C:\BizTalk_Files\MW\Maersk\Documentations\JKL-PetiksHour-XXX.xml', SINGLE_BLOB) AS x;

-- End of TEST

DECLARE

@XmlAltered XML

,@XmlAsNVarChar NVarChar(MAX)

SET @XmlAsNVarChar = CAST(@Xml AS nvarchar(MAX))

SET @XmlAsNVarChar = REPLACE(@XmlAsNVarChar, 'xmlns=""', SPACE(0))

SET @XmlAltered = CAST(@XmlAsNVarChar AS xml)

DECLARE @GUID uniqueidentifier

SET @GUID = NEWID()

SELECT

col.value('data(ShipNumber[1])', 'int') as ShipNumber

,col.value('data(AccountPeriod[1])', 'int') as AccountPeriod

,col.value('data(PersonEmployeeId[1])', 'int') as PersonEmployeeId

,col.value('data(RankCode[1])', 'int') as RankCode

,col.value('data(TotalNonConformityDeviation[1])', 'decimal(20,2)') as TotalNonConformityDeviation

,col.value('data(TotalWorkhours[1])', 'decimal(20,2)') as TotalWorkhours

--,col.value('(PetiksHourDays/PetiksHourDay/PeriodDate)[1]', 'varchar(10)') as PeriodDate

--,col.value('(PetiksHourDays/PetiksHourDay/Comments)[1]', 'varchar(MAX)') as Comments

--,col.value('(PetiksHourDays/PetiksHourDay/PetiksHourApplicableIn24hHourPeriod)[1]', 'decimal(20,2)') as PetiksHourApplicableIn24hHourPeriod

--,col.value('(PetiksHourDays/PetiksHourDay/PetiksHourApplicableIn7DayPeriod)[1]', 'decimal(20,2)') as PetiksHourApplicableIn7DayPeriod

,row2.ColDays.value('data(PeriodDate[1])','varchar(10)') as PeriodDate

,row2.ColDays.value('data(Comments)[1]', 'varchar(MAX)') as Comments

,row2.ColDays.value('data(PetiksHourApplicableIn24hHourPeriod[1])', 'decimal(20,2)') as PetiksHourApplicableIn24hHourPeriod

,row2.ColDays.value('data(PetiksHourApplicableIn7DayPeriod[1])', 'decimal(20,2)') as PetiksHourApplicableIn7DayPeriod

,row3.ColDayTime.value('data(PeriodDateTime[1])', 'DateTime') as PeriodDateTime

,row3.ColDayTime.value('data(PetiksHourIntervalCode[1])', 'varchar(5)') as PetiksHourIntervalCode

,row3.ColDayTime.value('data(PeriodValue[1])', 'varchar(1)') as PeriodValue

FROM

@XmlAltered.nodes('/PetiksHours/PetiksHour') tbl(col)

cross apply col.nodes('PetiksHourDays/PetiksHourDay') row2(ColDays)

cross apply col.nodes('PetiksHourDays/PetiksHourDay/PetiksHourDayTimes/PetiksHourDayTime') row3(ColDayTime)) x

Comments