I have a table (see below) that contains a series of name/value pairs for a document. The document is identified by the DocumentId column. The name and values are given by the FieldName and FieldValue columns. There is also a column called Changed (which is true when the FieldValue has been changed).
I need to produce an XML document like that shown below the table. The elements are the name/value pairs. Note that Changed must be an attribute of the element.
Do i need to pivot the data first, and then generate the xml?
How should the query look like?
Id DocumentId FieldName FieldValue Changed
161 9 text1 Another form. 0
163 9 text2 Soon it is time for lunch.!. 1
165 9 colour 3 0
<text1 changed=”0”>Another form</text1>
<text2 changed=”1”> Soon it is time for lunch.!</text2>