August 15, 2018 at 2:22 am
drop table #TestTable
CREATE TABLE #TestTable(
[id] [varchar](20) NULL,
[seq] [varchar](20) NULL,
[varchar](2000) NULL,
[link_desc] [varchar](254) NULL,
[text] [varchar](20) NULL
)
INSERT INTO #TestTable ([id],[seq],,[link_desc],[text])
SELECT '1001','1','link abc','desc abc', 'abc' union all
SELECT '1001','1','link def','desc def', 'def' union all
SELECT '1001','1','link ghi','desc ghi', 'ghi' union all
SELECT '1001','2','link jkl','desc jkl', 'jkl' union all
SELECT '1001','2','link mno','desc mno', 'mno' union all
SELECT '1001','3','link pqr','desc pqr', 'pqr'
;
WITH TestTableCTE AS
(
SELECT *, DENSE_RANK() OVER(ORDER BY [id], seq) AS rn
FROM #TestTable
)
SELECT DISTINCT x.*
FROM TestTableCTE
CROSS APPLY
(
VALUES
(1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, rn),
(2, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, rn),
(3, 2, null, NULL, id, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, rn),
(4, 2, NULL, null, NULL, null, NULL, NULL, NULL, NULL, NULL, NULL, NULL, rn),
(5, 1, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, rn),
(6, 5, NULL, NULL, NULL, null, NULL, 'Header value', NULL, NULL, NULL, NULL,NULL, rn),
(7, 5, NULL, NULL, NULL, NULL, null, null, link,link_desc, NULL, NULL, NULL, rn),
(8, 5, NULL, NULL, NULL, NULL, NULL, NULL, null, NULL, null, NULL, NULL, rn),
(9, 1, NULL, NULL, NULL, NULL, NULL, NULL, null,null, null, NULL, NULL,rn),
(10,1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, null , null, NULL,rn)
) x(
Tag,
Parent,
[CustomerNotes!1],
[Customer!2],
[CustId!3],
[UID!4],
[Parent!5],
[Header!6],
[ref!7!html!CDATA],
[ref!7],
[addid!8],
[pnb!9],
[custtype!10],
[Sort!11!SortID!hide]
)
ORDER BY [Sort!11!SortID!Hide]
for xml explicit
Actual Output
<CustomerNotes>
<Customer>
<CustId>1001</CustId>
<UID />
</Customer>
<Parent>
<Header>Header value</Header>
<ref>
<html><![CDATA]></html>desc abc</ref>
<ref>
<html><![CDATA]></html>desc def</ref>
<ref>
<html><![CDATA]></html>desc ghi</ref>
<addid />
</Parent>
<pnb />
<custtype />
</CustomerNotes>
<CustomerNotes>
<Customer>
<CustId>1001</CustId>
<UID />
</Customer>
<Parent>
<Header>Header value</Header>
<ref>
<html><![CDATA]></html>desc jkl</ref>
<ref>
<html><![CDATA]></html>desc mno</ref>
<addid />
</Parent>
<pnb />
<custtype />
</CustomerNotes>
Expected output
<CustomerNotes>
<Customer>
<CustId>1001</CustId>
<UID />
</Customer>
<Parent>
<Header>Header value</Header>
<ref>
<html><![CDATA]></html>desc abc
<html><![CDATA]></html>desc def
<html><![CDATA]></html>desc ghi
</ref>
<addid />
</Parent>
<pnb />
<custtype />
</CustomerNotes>
<CustomerNotes>
<Customer>
<CustId>1001</CustId>
<UID />
</Customer>
<Parent>
<Header>Header value</Header>
<ref>
<html><![CDATA]></html>desc jkl
<html><![CDATA]></html>desc mno
</ref>
<addid />
</Parent>
<pnb />
<custtype />
</CustomerNotes>
[/code]
I get that extra ref tag around html.Above is my expected format. I would like to remove those or not generate them. Its turning out to be more difficult than i thought.
Thanks for your help.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply