在 SQL 中创建 XML(用于 XML 显式)

需要一点帮助才能在SQL Server中创建一些XML。

生成的 XML 应如下所示：

<?xml version="1.0" encoding="utf-8"?>
<Root Version="2.17" KFZ="XX-DD 389">
  <Head name="ExecuteAnswer">
    <Key name="AnswerStatus" value="-OK" />
    <Key name="MsgId"><![CDATA[KFHB0907896aAUH223]]></Key>
    <Section name="Command">
      <Key name="Name" value="SetNewCommand" />
      <Key name="Param0"><![CDATA[XX-DD 389]]></Key>
      <Key name="Param1"><![CDATA[]]></Key>
      <Key name="Param2"><![CDATA[0987asfdsafhdsSS]]></Key>
      <Key name="Param3"><![CDATA[0097aSSSHSDOPir0]]></Key>
    </Section>
  </Head>
  <Data></Data>
</Root>

我无法将<Section>添加到XML，如果我添加它[Section!3!name]则会出现此错误：

错误 -> 键将被部分覆盖

法典：

SELECT 
   1 AS Tag, 
   NULL AS Parent,
   '2.17' AS [Root!1!Version],
   'XX-DD 389' AS [Root!1!KFZ],
   NULL AS [Head!2!name!Element],
   NULL AS [Key!3!name],
   NULL AS [Key!3!value],
   NULL AS [Key!3!CDATA]
UNION ALL               
SELECT 
    2 as Tag,  
    1 as Parent,    
    NULL,
    NULL,
    'ExecuteAnswer',
    NULL,
    NULL,
    NULL
UNION ALL 
SELECT 
    3 as Tag,  
    2 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    'AnswerStatus',
    '-OK',
    NULL
UNION ALL 
SELECT 
    3 as Tag,  
    2 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    'MsgId',
    NULL,           
    'KFHB0907896aAUH223'
FOR XML EXPLICIT

谁能帮我？

问候亚历克斯

正如评论中指出的那样，没有理由再CDATA了......它在语义上与正确转义的正常text()节点完全相同。

但是，有时遗留系统或第三方工具需要它。

您必须知道的事情：除了强制转换以NVARCHAR(MAX)并手动追加它之外，没有机会将xml declaration添加到 XML。

因此，如果您无论如何都必须检查字符串，您可以使用 FOR XML PATH() 创建干净简单的 XML，将CDATA创建为<xdata>content</xdata>并执行简单的REPLACE将这些占位符更改为CDATA开头和结尾字符。

但是：这是一种具有EXPLICIT的方法

SELECT 
   1 AS Tag, 
   NULL AS Parent,
   '2.17' AS [Root!1!Version],
   'XX-DD 389' AS [Root!1!KFZ],
   NULL AS [Head!2!name!Element],
   NULL AS [Key!3!name],
   NULL AS [Key!3!value],
   NULL AS [Key!3!!CDATA],
   NULL AS [Section!4!name],
   NULL AS [Key!5!name],
   NULL AS [Key!5!value],
   NULL AS [Key!6!name],
   NULL AS [Key!6!!CDATA]
UNION ALL               
SELECT 
    2 as Tag,  
    1 as Parent,    
    NULL,
    NULL,
    'ExecuteAnswer',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
UNION ALL 
SELECT 
    3 as Tag,  
    2 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    'AnswerStatus',
    '-OK',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
UNION ALL 
SELECT 
    3 as Tag,  
    2 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    'MsgId',
    NULL,           
    'KFHB0907896aAUH223',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
    UNION ALL 
SELECT 
    4 as Tag,  
    2 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    NULL,
    NULL,           
    NULL,
    'Command',
    NULL,
    NULL,
    NULL,
    NULL
    UNION ALL 
SELECT 
    5 as Tag,  
    4 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    NULL,
    NULL,           
    NULL,
    NULL,
    'Name',
    'SetNewCommand',
    NULL,
    NULL
    UNION ALL 
SELECT 
    6 as Tag,  
    4 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    NULL,
    NULL,           
    NULL,
    NULL,
    NULL,
    NULL,
    'Param0',
    'XX-DD 389'
FOR XML EXPLICIT

相关内容

最新更新

热门标签：