DB2 to SQL conversion
-
I ned to convert below DB2 query to SQL
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',DEL.COVERAGE_CODE))) as VARCHAR(1024)), 3) as COVERAGE_CODE
I am not able to get equivalents of xml related things in SQL. Any help would be appreciated
-
I ned to convert below DB2 query to SQL
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',DEL.COVERAGE_CODE))) as VARCHAR(1024)), 3) as COVERAGE_CODE
I am not able to get equivalents of xml related things in SQL. Any help would be appreciated
Search for xml-equivalents in TSQL: [Using XML Serialization with SQL's FOR XML PATH](https://www.codeproject.com/Articles/21379/Using-XML-Serialization-with-SQL-s-FOR-XML-PATH) [Using XML Serialization with C# and SQL Server - TechNet Articles - United States (English) - TechNet Wiki](https://social.technet.microsoft.com/wiki/contents/articles/28151.using-xml-serialization-with-c-and-sql-server.aspx) [](https://msdn.microsoft.com/en-us/library/jj161200(v=sql.105).aspx?f=255&MSPPError=-2147217396)
-
I ned to convert below DB2 query to SQL
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',DEL.COVERAGE_CODE))) as VARCHAR(1024)), 3) as COVERAGE_CODE
I am not able to get equivalents of xml related things in SQL. Any help would be appreciated
For that specific query, there are various equivalent methods in SQL: Concatenating Row Values in Transact-SQL - Simple Talk[^]
STUFF
(
(
SELECT ', ' + DEL2.COVERAGE_CODE
FROM YourTable As DEL2
WHERE DEL2.GroupingField = DEL.GroupingField
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)'),
1, 2, ''
)Or, if you're using SQL 2017, you can use the new
STRING_AGGfunction: STRING_AGG (Transact-SQL) | Microsoft Docs[^]STRING_AGG(DEL.COVERAGE_CODE, ', ')
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
