1

I have SQL where I need to render the start and end XML tags as <PropName></PropName> NOT <PropName /> when the value is NULL.

I know this is not the standard but I have a business reason to do it this way. I have tried several variations of the FOR XML PATH ('') statement but can't seem to get it to render as <PropName></PropName>. 

select [PropName] as '*' for xml path(''), type) as [PropName]

renders <PropName /> How do I change it to render as <PropName></PropName> ?

Tomalak
  • 332,285
  • 67
  • 532
  • 628
Steve
  • 39
  • 4
  • use sigle incline quote to indicate code `\`` – Juan Carlos Oropeza Oct 06 '15 at 15:44
  • 2
    `` and `` are 100% the same thing. You never have to use one or the other **unless** the tools that you use to process the XML are unsuitable or broken. In that case you have to fix your tools, not your XML. – Tomalak Oct 06 '15 at 15:45
  • @Tomalak OP already address that issue `I know this is not the standard but I have a business reason` – Juan Carlos Oropeza Oct 06 '15 at 15:46
  • @Juan The only way to actually address this issue is by fixing the consumer of the XML. – Tomalak Oct 06 '15 at 15:48
  • Tomalak is right, whatever code "needs" it to be incorrect is what you need to fix, not the query that's generating perfectly valid XML. – I wrestled a bear once. Oct 06 '15 at 15:49
  • also, in future posts you should specify which SQL you're using.. – I wrestled a bear once. Oct 06 '15 at 15:50
  • @Steve: Bottom line line is this: To my knowledge you cannot make SQL Server serialize the XML one way or the other. The contract is that it will give you syntactically valid XML, and that's what it does. The option to choose between `` and `` does not exist because it is a meaningless distinction. Any tool that consumes XML is *required* to understand both variants. If you have a tool in your processing chain that uses, say, some horrible regexp to pick apart the XML, then yeah, that's the spot where everything will blow up. – Tomalak Oct 06 '15 at 15:58

2 Answers2

1

Thanks everyone for your feedback. I get it that "<"PropName">""<"/PropName">" and "<"PropName /">" are 100% the same thing and I get it that whoever needs it should fix it on their end. The file that is produced is sent to a third party vendor and they are adamant it be rendered as . Fortunately a coworker identified I simply need to put ", TYPE" at the end of my outer SELECT statement, not in the sub select where the PropName resides, and that took care of it - who would have guessed? I suppose I should have mentioned that my PropName was in a sub select; perhaps that would have helped with the answer.

Steve
  • 39
  • 4
1

you found your solution already. Just to get some light into this...

All statements like "change the reader, not the XML" are right of course. But sometimes we have to fullfill third party requirements we cannot change.

There are some tricks to deal with the generation of empty XML elements. Paste this into an empty query window and execute. Examine the results...

--The NULL-element is not there at all
SELECT 'text' AS filled
      ,'' AS empty
      ,NULL AS NotThere
FOR XML PATH('row');   

--The NULL-element is rendered using "nil"
SELECT 'text' AS filled
      ,'' AS empty
      ,NULL AS NotThere
FOR XML PATH('row'),ELEMENTS XSINIL    

--Look at this: Both columns are called "TheName". They are implicitly concatenated
SELECT 'a' AS TheName
      ,'b' AS TheName
FOR XML PATH('row')

--That leads to: Concatenate nothing with an empty string will at least return the empty string.
--this is other/better than ISNULL, because it will work with any type...
SELECT NULL AS TheName
      ,'' AS TheName
FOR XML PATH('row')

DECLARE @tbl TABLE(int1 INT, int2 INT);
INSERT INTO @tbl VALUES(NULL,1);

--int1 is missing
SELECT *
FROM @tbl
FOR XML PATH('row');  

--both elements are there
SELECT int1, '' AS int1 --ISNULL(int1,'') would not compile...
      ,int2, '' AS int2
FROM @tbl
FOR XML PATH('row');

And finally: If you really need such a conversion you could create the XML, cast it to VARCHAR and use Replace to change all <PropName /> to <PropName></PropName>. I have to wash my hands now ;-)

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Thanks for your XML tricks! I will keep them in a safe place for future reference. – Steve Oct 08 '15 at 17:11
  • @Steve, Thx for your comment. Just saw that there was a final `FOR XML PATH` missing. So the last example did not show up correctly. Sorry for that... You found this yourself probably. If you liked this answer, please vote up and - if you want so - mark as accepted (the check below the vote counter). All people giving answers here are hungry for reputation points :-) – Shnugo Oct 08 '15 at 19:15