如何让 SQL 生成开始和结束 XML 标记 [英] How do I get SQL to generate both the starting and ending XML tags

问题描述

我有 SQL 需要将开始和结束 XML 标记呈现为 NOT 当值为 NULL.

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

我知道这不是标准，但我有商业理由这样做.我尝试了 FOR XML PATH ('') 语句的几种变体，但似乎无法将其呈现为 .

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> ?

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

推荐答案

您已经找到了您的解决方案.只是为了对此有所了解...

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

改变阅读器，而不是 XML"之类的所有陈述当然是正确的.但有时我们必须满足我们无法更改的第三方要求.

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.

有一些技巧可以处理空 XML 元素的生成.将其粘贴到一个空的查询窗口中并执行.检查结果...

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');

最后:如果您真的需要这样的转换，您可以创建 XML，将其转换为 VARCHAR 并使用 Replace 将所有 更改为.我现在必须洗手;-)

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 ;-)

这篇关于如何让 SQL 生成开始和结束 XML 标记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！