Shredding XML into SQLServer2005 tables
-
Maybe this?
SELECT m.value('(StaffDetails/NINumber)[1]','VARCHAR(10)') AS NINumber,b.value('FirstDayOfAbsence[1]','VARCHAR(10)') AS FirstDayOfAbsence,
b.value('LastDayOfAbsence[1]','VARCHAR(10)') AS LastDayOfAbsence
FROM @doc.nodes('/Message/SchoolWorkforceMembers/SchoolWorkforceMember') AS R(m)
OUTER APPLY m.nodes('Absences/Absence') AS A(b)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
😀 Fantastic! Just the job. Thank you so much!!
-
Spoke too soon! Further examination gives another problem
From this
<SchoolWorkforceMember>
<StaffDetails>
<StaffMemberName>
<PersonFamilyName>MOUSE</PersonFamilyName>
<GivenNames>
<GivenName>
<PersonGivenName>Mickey</PersonGivenName>
</GivenName>
</GivenNames>
</StaffMemberName>
<NINumber>XX1111111XX</NINumber>
<GenderCurrent>2</GenderCurrent>
<PersonBirthDate>1901-01-01</PersonBirthDate>
<Ethnicity>UNK</Ethnicity>
<Disability>NOBT</Disability>
<HLTAStatus>No</HLTAStatus>
<AbsentOnCensusDay>Y</AbsentOnCensusDay>
</StaffDetails>
<ContractOrServiceGroup>
<ContractOrService>
<ContractType>PRM</ContractType>
<ContractStart>2008-09-01</ContractStart>
<Post>UNA</Post>
<SchoolArrivalDate>1998-09-07</SchoolArrivalDate>
<Origin>OTHEDU</Origin>
<LASchoolLevel>S</LASchoolLevel>
<Estab>9999</Estab>
<PostLevelDetails>
<Payments>
<Scale>OT</Scale>
<RegionSpine>EW</RegionSpine>
<SalaryAmount>15825</SalaryAmount>
<SafeguardedSalary>false</SafeguardedSalary>
</Payments>
<Hours>
<HoursPerWeek>12.50</HoursPerWeek>
<FTEHours>37.00</FTEHours>
<WeeksPerYear>48</WeeksPerYear>
</Hours>
</PostLevelDetails>
<Roles>
<Role>
<RoleIdentifier>TASS</RoleIdentifier>
</Role>
</Roles>
</ContractOrService>
<ContractOrService>
<ContractType>TMP</ContractType>
<ContractStart>2008-09-01</ContractStart>
<Post>UNA</Post>
<SchoolArrivalDate>1998-09-07</SchoolArrivalDate>
<Origin>OTHEDU</Origin>
<LASchoolLevel>S</LASchoolLevel>
<Estab>9999</Estab>
<PostLevelDetails>
<Payments>
<Scale>OT</Scale>
<RegionSpine>EW</RegionSpine>
<SalaryAmount>15825</SalaryAmount>
<SafeguardedSalary>false</SafeguardedSalary>
</Payments>
<Hours>
<HoursPerWeek>3.50</HoursPerWeek>
<FTEHours>37.00</FTEHours>
<WeeksPerYear>48</WeeksPerYear>
</Hours>
</PostLevelDetails>
<Roles>
<Role>
<RoleIdentifier>TASS</RoleIdentifier>
</Role>
</Roles>
</ContractOrService>
<ContractOrService>
<ContractType>FXT</ContractType>
<ContractStart>2008-11-03</ContractStart>
<ContractEnd>2008-12-31</ContractEnd>
<Post>UNA</Post>
<SchoolArrivalDate>1998-09-07</SchoolArrivalDate>
<DestinationCode>LEAPRM</DestinationCode>
<Origin>OTHEDU</Origin>
<LASchoolLevel>S</LASchoolLevel>
<Estab>9999</Estab>
<PostLevelDetails>
<Hours>
<HoursPerWeek>2.00</HoursPerWeek>
<FTEHours>37.00</FTEHours>
<WeeksPerYear>48</WeeksPerYear>
</Hours>
</PostLevelDetails>
<Roles>
<Role>
<RoleIdentifier>TASS</RoleIdentifier>
</Role>
</Roles>
</ContractOrService>
</ContractOrServiceGroup>
</SchoolWorkforceMember>
I need to get the NINumber, ContractStart and hours out in nice neat rows, like this
XX111111XX 2008-09-01 12.50
XX111111XX 2008-09-01 3.50
XX111111XX 2008-11-03 2.00
Any ideas? I've tried adapting the previous answer, but either get multiplied rows or the same ContractStart for all three.
Thanks in anticipation!
-
SELECT m.value('(StaffDetails/NINumber)[1]','VARCHAR(10)') AS NINumber,b.value('ContractStart[1]','VARCHAR(10)') AS ContractStart,
b.value('(PostLevelDetails/Hours/HoursPerWeek)[1]','DECIMAL(10,2)') AS Hours
FROM @doc.nodes('/SchoolWorkforceMember') AS R(m)
OUTER APPLY m.nodes('ContractOrServiceGroup/ContractOrService') AS A(b)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Thanks, exactly what I needed!
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply