时间戳

首页 > 解决方案 > 带有子元素的 CDATA

sql-server - 带有子元素的 CDATA

问题描述

我想要 MakeType 子集的结果。现在我得到了整个块，而不是 MakeType 的元素。当 XML 解析 Type、Series 和 class 标记时，应该为它们创建一个元素。这是在 SQL Server Standard 2017 上。而且我真的不知道 CDATA xml 样式并且不会使用它，但供应商需要 CDATA 类型。

       ---Create Temp Table         
            declare @RepCar table
            (
            [Name] varchar(10),
            [Make] varchar(10),
            [Model] varchar(10),
            [Price]  money,
            [Type] varchar(10),
            [Series]  varchar(10),
            [Class] Varchar(10)
            );
             insert into @RepCar
            (
            Name, Make, Model, Price, Type, Series, Class
            )
            values
            ('Car1', 'Make1', 'Model1', 100, 'Type1', 'IS', 'Sedan'),
            ('Car1', 'Make1', 'Model1', 100, 'Type1', 'LS' , 'Sport'),
            ('Car2', 'Make2', 'Model2', 200, 'Type2', 'M3' , 'Sport'),
            ('Car3', 'Make3', 'Model3', 300, 'Type3','GS350','Sedan');




           --Declare Variables   
            DECLARE @TransactionId NVARCHAR(100)
            DECLARE @TransactionDateTime DATETIME
            --Setting Variable
            SET @TransactionId= (SELECT CONVERT(VARCHAR, CURRENT_TRANSACTION_ID()))
            SET @TransactionDateTime= GETDATE()

          --Create the XML

            select 1 AS Tag,
            0 AS Parent,
            'CollectSamplingData' as 'Message!1!TransactionType!cdata',
            @TransactionId as 'Message!1!TransactionID!cdata',
            @TransactionDateTime  as 'Message!1!TransactionDate!cdata',
            [Name]  as 'Message!1!CName!cdata',
            [Make]  as 'Message!1!MakeCar!cdata',
            [Model]  as 'Message!1!MakeModel!cdata',
            [Price]   as 'Message!1!DataValue!cdata',
            [Type]  as 'Message!1!MakeType!cdata' ,

         -----This is the SQL that is'nt working.
            ( select 
             1 AS Tag,
             0 AS Parent,
            [Series]  as 'Message!2!MakeSeries!cdata',
             [Class]  as 'Message!2!MakeClass!cdata' 
               from @RepCar  
             FOR XML EXPLICIT 
            )
            from @RepCar 
            FOR XML EXPLICIT, ROOT('Message');

结果应该如下所示。当代码确实看到时，MakeType 应该具有下面的系列和类作为子元素。这些是所需的输出 XML

     <Message>
        <Message>
        <TransactionType><![CDATA[CollectSamplingData]]></TransactionType>
        <TransactionID><![CDATA[1482282230]]></TransactionID>
        <TransactionDate><![CDATA[2020-02-03T11:05:17.340]]></TransactionDate>
        <CName><![CDATA[Car1]]></CName>
        <MakeCar><![CDATA[Make1]]></MakeCar>
        <MakeModel><![CDATA[Model1]]></MakeModel>
        <DataValue><![CDATA[100.0000]]></DataValue>
        <MakeType><![CDATA[Type1]]>
                           <Series><![CDATA[IS]></Series>
                           <Class><![CDATA[Sedan]]></Class>
                           <Series><![CDATA[LS]></Series>
                           <Class><![CDATA[Sport]]></Class>
                           <Series><![CDATA[M3]></Series>
                           <Class><![CDATA[Sport]]></Class>
                           <Series><![CDATA[GS350]></Series>
                           <Class><![CDATA[Sedan]]></Class>>


            </MakeType>
       </Message>

标签： sql-serverxmlcdata

解决方案

只是为了比较，我想展示当 XQuery 引擎完全支持标准时实现 CDATA 部分是多么容易。下面是使用cdata-section-elements序列化参数的 BaseX 9.3.1 实现：要作为 CDATA 输出的元素列表，由空格分隔。

两个元素<city>，并<motto>以简单的声明方式作为 CDATA 部分发出。

XQuery

xquery version "3.1";
declare option output:omit-xml-declaration "no";
declare option output:cdata-section-elements "city motto";

declare context item := document {
<root>
  <row>
    <state>FL</state>
    <motto>In God We Trust</motto>
    <city>Miami</city>
  </row>    
  <row>
    <state>NJ</state>
    <motto>Liberty and Prosperity</motto>
    <city>Trenton</city>
  </row>
</root>
};

<root>
{
  for $r in ./root/row
  return $r
}
</root>

输出

<?xml version="1.0" encoding="UTF-8"?>
<root>
  <row>
    <state>FL</state>
    <motto><![CDATA[In God We Trust]]></motto>
    <city><![CDATA[Miami]]></city>
  </row>
  <row>
    <state>NJ</state>
    <motto><![CDATA[Liberty and Prosperity]]></motto>
    <city><![CDATA[Trenton]]></city>
  </row>
</root>

推荐阅读