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>
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) = '<'
, @gt NCHAR(4) = '>';
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>