CDATA with Child elements

Question

I would like the outcome with a subset of the MakeType . Right now I get the entire block and not elements of the MakeType. When the XML resolves the Type, the Series and class tag an element for those should be created. This is on SQL Server Standard 2017.And I really don't know CDATA xml style and would not use it but the vendor requires the CDATA type.

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

The Outcome should look like this.When the code does see a the MakeType should have the Series and class below as the child element. These are the desired output 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>

Gatorsdog, there is a mismatch between (1) sample data and (2) the desired output as XML. Please modify your post to make them matching. And your desired XML is not well-formed. — Yitzhak Khabinsky, Feb 03 '20 at 17:05
Whoever made this a requirement has no understanding at all of XML. Very few tools will allow you to produce XML output like this, because no sane user would want to receive XML output like this. — Michael Kay, Feb 04 '20 at 00:27

Yitzhak Khabinsky · Accepted Answer · 2020-02-03T21:39:22.117

I struggled to produce what you need by using FOR XML EXPLICIT. Eventually, I reverted to using XQuery FLWOR expression. Please remember that SQL Server XML data type cannot hold CDATA sections. You need to use the NVARCHAR(MAX) data type. Check it out here: How to use CDATA in SQL XML

SQL

-- DDL and sample data population, start
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');
-- DDL and sample data population, end

--Declare Variables   
DECLARE @TransactionId NVARCHAR(100) = CURRENT_TRANSACTION_ID();
DECLARE @TransactionDateTime DATETIME = GETDATE();

DECLARE @lt NCHAR(4) = '&lt;'
    , @gt NCHAR(4) = '&gt;';

SELECT REPLACE(REPLACE(TRY_CAST((SELECT 'CollectSamplingData' AS [TransactionType]
    , @TransactionId AS [TransactionID]
    , @TransactionDateTime AS [TransactionDate]
    , * 
FROM @RepCar
FOR XML PATH('r'), TYPE, ROOT('root')).query('<Messages><Message>
{
for $x in /root/r[1]
return (<TransactionType>{concat("<![CDATA[", data($x/TransactionType[1]), "]]>")}</TransactionType>,
        <TransactionID>{concat("<![CDATA[", data($x/TransactionID[1]), "]]>")}</TransactionID>,
        <TransactionDate>{concat("<![CDATA[", data($x/TransactionDate[1]), "]]>")}</TransactionDate>,
        <CName>{concat("<![CDATA[", data($x/Name[1]), "]]>")}</CName>,
        <MakeCar>{concat("<![CDATA[", data($x/Make[1]), "]]>")}</MakeCar>,
        <MakeModel>{concat("<![CDATA[", data($x/Model[1]), "]]>")}</MakeModel>,
        <DataValue>{concat("<![CDATA[", data($x/Price[1]), "]]>")}</DataValue>,
        <MakeType>{concat("<![CDATA[", data($x/Type[1]), "]]>")}
        {
            for $y in /root/r
            return (
                <Series>{concat("<![CDATA[", data($y/Series[1]), "]]>")}</Series>,
                <Class>{concat("<![CDATA[", data($y/Class[1]), "]]>")}</Class>
            )
        }
    </MakeType>)
}
</Message></Messages>') AS NVARCHAR(MAX)), @lt,'<'), @gt, '>') AS [XML with CDATA sections];

Output

<Messages>
    <Message>
        <TransactionType><![CDATA[CollectSamplingData]]></TransactionType>
        <TransactionID><![CDATA[1149709]]></TransactionID>
        <TransactionDate><![CDATA[2020-02-03T16:23:43.020]]></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>
</Messages>

This looks great however when the XML is created and I click the column the XML doesn't show in another SSMS window. — Gatorsdog, Feb 04 '20 at 20:07
Because the XML is held in the VARCHAR(MAX) data type. Just XML data type is clickable. My answer has a link to learn more about it. Here it is again: https://stackoverflow.com/questions/51729982/how-to-use-cdata-in-sql-xml — Yitzhak Khabinsky, Feb 04 '20 at 20:58
Good to hear that the proposed solution is working for you. Please mark it as answered. You simply need to mark an answer as correct (the green check image). Click the green outlined checkmark to the left of the answer that solved your problem. This marks the answer as "accepted" — Yitzhak Khabinsky, Feb 05 '20 at 15:20

Yitzhak Khabinsky · Answer 2 · 2020-02-04T04:41:01.137

Just for comparison, I would like to show how easy to implement CDATA section when the XQuery engine fully supports standards. Below is BaseX 9.3.1 implementation which is using cdata-section-elements serialization parameter: List of elements to be output as CDATA, separated by whitespaces.

Two elements <city> and <motto> are emitted as CDATA section in a simple declarative way.

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>

Output

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

CDATA with Child elements

2 Answers2