• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

Query syntax

Hello,

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

Open in new window


<Dataset>
<DocumentId>9</DocumentId>
<text1 changed=”0”>Another form</text1>
<text2 changed=”1”> Soon it is time for lunch.!</text2>
<colour changed=”0”>3</color>
<Dataset>

Open in new window

0
soozh
Asked:
soozh
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here is the way I would likely do... surely other ways to shave the cat
using a table variable for testing:
declare @t table ( Id int ,	DocumentId	int , FieldName	varchar(10), FieldValue	                     varchar(100),  Changed bit )
insert into @t  values (161	,9	                 ,'text1','Another form.',	                        0)
insert into @t  values (163	,9	                 ,'text2','Soon it is time for lunch.!.',	       1)
insert into @t  values (165	,9	                 ,'colour','3',                                         0)


select '<Dataset><DocumentId>' + cast(DocumentID as varchar(100)) + '</DocumentId>'
	+ replace(replace(( select '<' + x.FieldName + ' changed="' + cast(x.changed as varchar(10)) + '">' + x.FieldValue +  '</' + x.FieldName + '>' 
	      from @t x
	     where x.DocumentId = t.DocumentId
		   for xml path('')
	   ) ,'&lt;', '<'), '&gt;', '>')
	+'</Dataset>'
from @t t
group by DocumentID

Open in new window

0
 
soozhAuthor Commented:
Thanks for the help.  I had to add some isnull statements because some of the columns had null values...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now