XML and SQL 2005 Help

• shaun-233951

  Grasshopper

  Points: 16

  More actions

  June 23, 2008 at 8:15 am

  #192851

  Hi,

  I was after some help in manipulating some xml data in SQL 2005. I have the following XML and what I am trying to do is select the PupilID, SessionsPossible,AttendanceReason and AbsenceSessions so that my results would look something like:

  12345 86 I 6

  12345 86 M 1

  I have got 1000's of record like this one and in some cases some of the information outlined above may be missing.

  Any help would be much appreciated

  <Pupil>

  <PupilIdentifiers>

  <PupilID>12345</PupilID>

  <Surname>BOB</Surname>

  <Forename>HOPE</Forename>

  <Gender>M</Gender>

  <DOB>1997-02-18</DOB>

  </PupilIdentifiers>

  <Attendance>

  <TermlyAttendance>

  <SessionsPossible>96</SessionsPossible>

  <SessionDetails>

  <SessionDetail>

  <AttendanceReason>I</AttendanceReason>

  <AbsenceSessions>6</AbsenceSessions>

  </SessionDetail>

  <SessionDetail>

  <AttendanceReason>M</AttendanceReason>

  <AbsenceSessions>1</AbsenceSessions>

  </SessionDetail>

  </SessionDetails>

  </TermlyAttendance>

  </Attendance>

  </Pupil>

• DB_Andrew

  Ten Centuries

  Points: 1089

  More actions

  July 9, 2008 at 1:59 pm

  #840491

  The code I'm providing is a start in the right direction. You however need to reformat your XML so your data is organized in a more normalized manner. Having the same node names repeat with in the same element is considered bad practice. It is like having a table with Name1, Name2, Name3. It is possible to make it work in the current format. I'm just too lazy to invest the time to figure it out. Its easier to go back and modify your object and change format of the XML.

  DECLARE @XML XML

  SET @XML = ' '

  DECLARE @xmldoc INT

  EXEC sp_XML_preparedocument @xmldoc OUTPUT, @xml

  SELECT

  PupilID AS PupilID

  ,SessionsPossible AS SessionsPossible

  ,AttendanceReason AS AttendanceReason

  ,AbsenceSessions AS AbsenceSessions

  FROM OPENXML (@xmldoc, '/Pupil[1]/PupilIdentifiers',2)

  WITH(

  PupilID INT

  ,SessionsPossible INT '/Pupil/Attendance/TermlyAttendance/SessionsPossible'

  ,AttendanceReason CHAR(1) '/Pupil/Attendance/TermlyAttendance/SessionDetails/SessionDetail/AttendanceReason'

  ,AbsenceSessions INT '/Pupil/Attendance/TermlyAttendance/SessionDetails/SessionDetail/AbsenceSessions'

  )

  EXEC sp_xml_removedocument @xmldoc