CODEWITHSUNDEEP
sqlsql-serverxml
developer
developer

Reputation: 25

SQL Date column in xml

StudentID ExamID  09/05/2017   08/05/2017  07/05/2017  06/05/2017  05/05/2017
123       AS12    12
123       AS13    13                23

While convert the above using "FOR XML PATH , Elements" in sql statement. I got the error.

error:Column name '09/05/2017' contains an invalid XML identifier as required by FOR XML; '2'(0x0032) is the first character at fault.

Is there any way I will get XML in format:

<row>
   <StudentID>123</StockID>
   <LessonID>AS13</LessonID>
   <09/05/2017>13</09/05/2017>
   <08/05/2017>23</08/05/2017>
   <07/05/2017></07/05/2017>
   <06/05/2017></06/05/2017>
   <05/05/2017></05/05/2017>
</row>

Upvotes: 0

Views: 78

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

It is a very bad design, to store your date-based values in columns of the student table. Whenever you have to add a column in order to add more data, the design is bad... This should be stored in a related side table, while a PIVOT query constructs this output format, whenever you need it.

And: Avoid culture specific date formats!!!

How should one know, wheter 06/05/2017 is the 6th of May or the 5th of June? Use ISO8601 like 2017-05-06 (which makes it sure, that you think about the 6th of May)

About your question: No, this is impossible!

XML does not allow an element's name like '05/05/2017'. You must start with a non-numeric character or an underscore and several characters like the / are forbidden...

Try to create your XML similar to

<row>
   <StudentID>123</StockID>
   <LessonID>AS13</LessonID>
   <Marks>
     <Mark date="2017-05-09">13<Mark>
     <Mark date="2017-05-08">23<Mark>
     [... more of them ...]
   </Marks>
</row>

Upvotes: 1

Jon Le
Jon Le

Reputation: 83

This error goes back to how to treat strings in the language you wish to program in. In this case once you are inside the brackets(<>) the slash is (/) is a special character and the first set of algorithms that process this (regex) XML identify the slash as an issue thereby throwing the error.

Additionally you may want to consider how you want to treat your objects in XML. First group is the class, the class has many students, and the students take many lessons, and each lesson has a grade. (or in this case it looks like a lesson has many grades, not shown here)

<CLASS>
   <STUDENT>
      <StudentID>123</StudentID>
      <LESSON>
         <LessonID>AS12</LessonID>
         <DATE>09/05/2017</DATE>
         <GRADE>93.00</GRADE>
      </LESSON>
      <LESSON>
         <LessonID>AS12</LessonID>
         <DATE>08/05/2017</DATE>
         <GRADE>93.00</GRADE>
      </LESSON>
   </STUDENT>
   <STUDENT>
      ...
   </STUDENT>
</CLASS>

Upvotes: 0

Related Questions