Shredding XML into SQLServer2005 tables
-
I need to shred an XML file into database tables. I can extract the Staff Details, but I need to load the Absences into a seperate table, linked with NI number so that I can add to it from a different source and then re-extract. Everything I've read suggests the select should do the job, but I can't seem to get the NINumber correct, I either get the same one for all four records, or null. Any ideas?
DECLARE @iDoc int
declare @XML as XML --varchar(max)
declare @file as varchar(100)
DECLARE @doc xml
SET @doc ='
<Message xmlns:msxsl="urn:schemas-microsoft-com:xslt">
<SchoolWorkforceMembers>
<SchoolWorkforceMember>
<StaffDetails>
<TeacherNumber>1111111</TeacherNumber>
<StaffMemberName>
<PersonFamilyName>TEST1</PersonFamilyName>
<GivenNames>
<GivenName>
<PersonGivenName>Name1</PersonGivenName>
</GivenName>
<GivenName>
<PersonGivenName>Name2</PersonGivenName>
</GivenName>
</GivenNames>
</StaffMemberName>
<NINumber>YY111111YY</NINumber>
<GenderCurrent>1</GenderCurrent>
<PersonBirthDate>1900-01-01</PersonBirthDate>
<Ethnicity>UNK</Ethnicity>
<Disability>NOBT</Disability>
<QTStatus>Yes</QTStatus>
<HLTAStatus>No</HLTAStatus>
<AbsentOnCensusDay>N</AbsentOnCensusDay>
</StaffDetails>
<Absences>
<Absence>
<FirstDayOfAbsence>2008-11-07</FirstDayOfAbsence>
<LastDayOfAbsence>2008-11-07</LastDayOfAbsence>
<WorkingDaysLost>0.5</WorkingDaysLost>
<AbsenceCategory>TRN</AbsenceCategory>
<Estab>4408</Estab>
</Absence>
<Absence>
<FirstDayOfAbsence>2008-11-14</FirstDayOfAbsence>
<LastDayOfAbsence>2008-11-14</LastDayOfAbsence>
<WorkingDaysLost>0.5</WorkingDaysLost>
<AbsenceCategory>TRN</AbsenceCategory>
<Estab>4408</Estab>
</Absence>
</Absences>
</SchoolWorkforceMember>
<SchoolWorkforceMember>
<StaffDetails>
<TeacherNumber>2222222</TeacherNumber>
<StaffMemberName>
<PersonFamilyName>TEST2</PersonFamilyName>
<GivenNames>
<GivenName>
<PersonGivenName>Name1</PersonGivenName>
</GivenName>
<GivenName>
<PersonGivenName>Name2</PersonGivenName>
</GivenName>
</GivenNames>
</StaffMemberName>
<NINumber>XX123456XX</NINumber>
<GenderCurrent>1</GenderCurrent>
<PersonBirthDate>1901-01-01</PersonBirthDate>
<Ethnicity>UNK</Ethnicity>
<Disability>NOBT</Disability>
<QTStatus>Yes</QTStatus>
<HLTAStatus>No</HLTAStatus>
<AbsentOnCensusDay>N</AbsentOnCensusDay>
</StaffDetails>
<Absences>
<Absence>
<FirstDayOfAbsence>2009-11-07</FirstDayOfAbsence>
<LastDayOfAbsence>2009-11-07</LastDayOfAbsence>
<WorkingDaysLost>0.5</WorkingDaysLost>
<AbsenceCategory>TRN</AbsenceCategory>
<Estab>4408</Estab>
</Absence>
<Absence>
<FirstDayOfAbsence>2009-11-14</FirstDayOfAbsence>
<LastDayOfAbsence>2009-11-14</LastDayOfAbsence>
<WorkingDaysLost>0.5</WorkingDaysLost>
<AbsenceCategory>TRN</AbsenceCategory>
<Estab>4408</Estab>
</Absence>
</Absences>
</SchoolWorkforceMember>
</SchoolWorkforceMembers>
</Message>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc,'/Message/SchoolWorkforceMembers/SchoolWorkforceMember/Absences/Absence', 2)
WITH (NiNumber varchar (10) '/Message/SchoolWorkforceMembers/SchoolWorkforceMember/StaffDetails/NINumber' ,
-- ** GIVES EVERYONE THE SAME NINUMBER
-- WITH (NiNumber varchar (10) '/Message/SchoolWorkforceMembers/SchoolWorkforceMember/StaffDetails' , **JUST GETS ALL STAFF DETAILS IN A STRING
FirstDayOfAbsence varchar(50) )
--remove xml doc
EXEC sp_xml_removedocument @iDoc
Hope someone can help me with my first post! Thanks!
:blush: Forgot to say that any solution has to work for a big file, not just the small extract shown here!
:w00t: Latest update! From your excellent forums I devised this
SELECT R.nref.value('../../NINumber[1]', 'varchar(10)') [NINumber],
R.nref.value('.','varchar(10)') [FirstDayOfAbsence]
FROM @doc.nodes('/Message/SchoolWorkforceMembers/SchoolWorkforceMember/Absences/Absence') AS R(nref)
which (if I remove the StaffDetails layer - which seems superflous anyway, and I can probably edit the XML file before processing to do that) works! Except I can't then seem to get any more than the first field from then Absences node, and ultimately I need all of them.
Any ideas for this angle?
-
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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply