Converting code in sql server with xml path into Teradata code.

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Teradata Employee

Converting code in sql server with xml path into Teradata code.

 Hello All,

 

I have following SQL server code which is using xml path and i want to convert this code into Teradata equivalent.

select distinct DateUpdated,
NewStatusId ,
ReasonId
from
(select COALESCE(EntityActionDatetime, CONVERT(varchar(100),cast('2099-01-01' as datetime) ,126)) DateUpdated,
cast(COALESCE(NewValue_ID,-1) as bigint) NewStatusId ,
COALESCE(Reason_ID,-1) ReasonId

from DW_DIM_AuditStatusHistory A
where A.EntityActionType_ID = 58
and A.CID = T.CID
UNION
select COALESCE(EntityActionDatetime, CONVERT(varchar(100),cast(acc.OperationalStatusChangeDate as datetime) ,126)) DateUpdated,
cast(COALESCE(NewValue_ID,COALESCE(ACC.OperationalStatus_ID, -1)) as bigint) NewStatusId ,
COALESCE(Reason_ID,COALESCE(OperationalStatusReason_ID,-1)) ReasonId

from DWH_VW.DW_DIM_Accounts ACC
left join DW_DIM_AuditStatusHistory B ON ACC.CID = B.CID and B.NewValue_ID = ACC.OperationalStatus_ID
and B.EntityActionType_ID = 58
where ACC.CID = T.CID
)x
For XML PATH ('StatusAudit') , root('StatusAudits');

 

 

Current documentation for XML stuff in Teradata is not having efficient examples, please suggest any source for learning Teradata xml. 


Accepted Solutions
Teradata Employee

Re: Converting code in sql server with xml path into Teradata code.

Hello Experts,

 

Found solution, if anybody still interested.

 SQL server:

cast(((select distinct DateUpdated,
NewStatusId ,
ReasonId
from dwhDW_DIM_Accounts ACC )x
For XML PATH ('StatusAudit') , root('StatusAudits'))
as nvarchar(max))

 

Teradata code:

select cast(xmlagg(XMLSERIALIZE(DOCUMENT XMLDOCUMENT
(xmlelement(name "StatusAudits",(XMLELEMENT(NAME "StatusAudit",
(XMLCONCAT(XMLELEMENT(NAME "NewStatusId",E.NewStatusId),
XMLELEMENT(NAME "ReasonId",E.ReasonId))))))))) as varchar(10000) character set unicode) from dwhDW_DIM_Accounts 

 

check out syntax as i have typed the query as i could not paste the actual query for some reason.

1 ACCEPTED SOLUTION
1 REPLY
Teradata Employee

Re: Converting code in sql server with xml path into Teradata code.

Hello Experts,

 

Found solution, if anybody still interested.

 SQL server:

cast(((select distinct DateUpdated,
NewStatusId ,
ReasonId
from dwhDW_DIM_Accounts ACC )x
For XML PATH ('StatusAudit') , root('StatusAudits'))
as nvarchar(max))

 

Teradata code:

select cast(xmlagg(XMLSERIALIZE(DOCUMENT XMLDOCUMENT
(xmlelement(name "StatusAudits",(XMLELEMENT(NAME "StatusAudit",
(XMLCONCAT(XMLELEMENT(NAME "NewStatusId",E.NewStatusId),
XMLELEMENT(NAME "ReasonId",E.ReasonId))))))))) as varchar(10000) character set unicode) from dwhDW_DIM_Accounts 

 

check out syntax as i have typed the query as i could not paste the actual query for some reason.