Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dn1969
Starting Member
3 Posts |
 Posted - 2008-09-08 : 14:01:16
|
| Our programmers created a data extract for one of our clients using the "FOR XML" option to generate the XML file that they asked for.The only problem that we have run into is that if a field is blank, SQL Server returns <Field /> and our client has specified that they need <Field></Field>.Is there a way to specify this in the FOR XML query?The full text of the FOR syntax we are using is as follows:FOR XML AUTO, ROOT('ROWSET'), ELEMENTSThanks in advance for any assistance! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-08 : 14:29:33
|
| Try with REPLACE function |
 |
|
|
dn1969
Starting Member
3 Posts |
Posted - 2008-09-08 : 14:47:18
|
quote:
Originally posted by visakh16
Try with REPLACE function
Thank you for the suggestion but this has the potential to be a very large dataset with a large number of tags, many of which can be empty and I'd rather not slow the extract down that much. It would be faster to explicitly select each element as text but I'd also like to avoid that if at all possible.Any other ideas related to the formatting of the actual XML?Thanks! |
|
|
|
dn1969
Starting Member
3 Posts |
Posted - 2008-09-08 : 15:16:58
|
| I'm not sure of the reason behind why this works but...The stored procedure being used was declaring an XML variable and then setting @XML to the "SELECT stuff FROM table FOR XML AUTO, ROOT('ROWSET'), ELEMENTS" and then SELECT @XML.This was resulting in the empty XML tags being returned as <tag />.We changes the procedure to not use an @XML variable and just returning "SELECT stuff FROM table FOR XML AUTO, ROOT('ROWSET'), ELEMENTS" in which case the empty tags are returned as <tag></tag>.Problem mysteriously solved. |
|
|
|
|
|
|
SQL Server and XML