August 6, 2018 at 10:19 am
Hi
trying to get this xml output correct using xml explicit. I need them to group the links together based on the siteid, header1 value,linkid and link_created_date?
DECLARE @TestTable TABLE (siteid int, header1 varchar(255),link varchar(255),linkid varchar(20), link_created_date date)
INSERT INTO @TestTable values (30001,'30001 header1','my link 1',30,GETDATE()-1)
INSERT INTO @TestTable values (30001,'30001 header1','my link 2',30,GETDATE()-1)
INSERT INTO @TestTable values (30001,'30001 header2','my link 3',70,GETDATE()-2)
INSERT INTO @TestTable values (30001,'30001 header2','my link 4',70,GETDATE()-3)
INSERT INTO @TestTable values (30002,'30002 header1','my link 5',102,GETDATE()-1)
INSERT INTO @TestTable values (30002,'30002 header1','my link 6',102,GETDATE()-1)
INSERT INTO @TestTable values (30003,'30003 header1','my link 7',106,GETDATE()-1)
INSERT INTO @TestTable values (30003,'30003 header2','my link 8',106,GETDATE()-1)
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [DynamicLinksFile!1],
NULL AS [DynamicLinks!2],
NULL AS [ExternalSite!3],
NULL AS [ExternalSiteId!4],
NULL AS [ExternalSystemId!5],
NULL AS [Link!6],
NULL AS [Header!7],
NULL AS [LinkPage!8!html!CDATA],
NULL AS [LinkPage!8],
NULL AS [AssignedValue!9],
NULL AS [linkid!10],
NULL AS [DynamicLinkType!11]
FROM @TestTable
UNION ALL
SELECT 2 AS Tag
,1 AS Parent
,NULL AS [DynamicLinksFile]
,NULL AS [DynamicLink]
,NULL AS [ExternalSite]
,NULL AS [ExternalSiteID]
,NULL AS [ExternalSystemId]
,NULL AS [Link]
,NULL AS [Header]
,NULL AS [LinkPage!8!html!CDATA]
,NULL AS [LinkPage!8]
,NULL AS [AssignedValue]
,NULL AS [linkid]
,NULL AS [DynamicLinkType]
FROM @TestTable
UNION ALL
SELECT 3 AS Tag
,2 AS Parent
,NULL AS [DynamicLinksFile]
,NULL AS [DynamicLink]
,NULL AS [ExternalSite]
,NULL AS [ExternalSiteID]
,NULL AS [ExternalSystemId]
,NULL AS [Link]
,NULL AS [Header]
,NULL AS [LinkPage!8!html!CDATA]
,NULL AS [LinkPage!8]
,NULL AS [AssignedValue]
,NULL AS [linkid]
,NULL AS [DynamicLinkType]
FROM @TestTable
UNION ALL
SELECT 4 AS Tag
,3 AS Parent
,NULL AS [DynamicLinksFile]
,NULL AS [DynamicLink]
,NULL AS [ExternalSite]
,siteid AS [ExternalSiteID]
,NULL AS [ExternalSystemId]
,NULL AS [Link]
,NULL AS [Header]
,NULL AS [LinkPage!8!html!CDATA]
,NULL AS [LinkPage!8]
,NULL AS [AssignedValue]
,NULL AS [linkid]
,NULL AS [DynamicLinkType]
FROM @TestTable
UNION ALL
SELECT 5 AS Tag
,3 AS Parent
,NULL AS [DynamicLinksFile]
,NULL AS [DynamicLink]
,NULL AS [ExternalSite]
,NULL AS [ExternalSiteID]
,2 AS [ExternalSystemId]
,NULL AS [Link]
,NULL AS [Header]
,NULL AS [LinkPage!8!html!CDATA]
,NULL AS [LinkPage!8]
,NULL AS [AssignedValue]
,NULL AS [linkid]
,NULL AS [DynamicLinkType]
FROM @TestTable
UNION ALL
SELECT 6 AS Tag
,2 AS Parent
,NULL AS [DynamicLinksFile]
,NULL AS [DynamicLink]
,NULL AS [ExternalSite]
,NULL AS [ExternalSiteID]
,NULL AS [ExternalSystemId]
,'Parent' AS [Link]
,NULL AS [Header]
,NULL AS [LinkPage!8!html!CDATA]
,NULL AS [LinkPage!8]
,NULL AS [AssignedValue]
,NULL AS [linkid]
,NULL AS [DynamicLinkType]
FROM @TestTable
UNION ALL
SELECT 7 AS Tag
,6 AS Parent
,NULL AS [DynamicLinksFile]
,NULL AS [DynamicLink]
,NULL AS [ExternalSite]
,NULL AS [ExternalSiteID]
,NULL AS [ExternalSystemId]
,NULL AS [Link]
,header1 AS [Header]
,NULL AS [LinkPage!8!html!CDATA]
,NULL AS [LinkPage!8]
,NULL AS [AssignedValue]
,NULL AS [linkid]
,NULL AS [DynamicLinkType]
FROM @TestTable
UNION ALL
SELECT 8 AS Tag
,6 AS Parent
,NULL AS [DynamicLinksFile]
,NULL AS [DynamicLink]
,NULL AS [ExternalSite]
,NULL AS [ExternalSiteID]
,NULL AS [ExternalSystemId]
,NULL AS [Link]
,NULL AS [Header]
,A.link AS [LinkPage!8!html!CDATA]
,null
,NULL AS [AssignedValue]
,NULL AS [linkid]
,NULL AS [DynamicLinkType]
FROM @TestTable A
JOIN @TestTable B ON
A.siteid=B.siteid
AND A.header1=B.header1
UNION ALL
SELECT 9 AS Tag,
2 AS Parent
,NULL AS [DynamicLinksFile]
,NULL AS [DynamicLink]
,NULL AS [ExternalSite]
,NULL AS [ExternalSiteID]
,NULL AS [ExternalSystemId]
,NULL AS [Link]
,NULL AS [Header]
,NULL AS [LinkPage!8!html!CDATA]
,NULL AS [LinkPage!8]
,3 AS [AssignedValue]
,NULL AS linkid
,NULL AS [DynamicLinkType]
FROM @TestTable
UNION ALL
SELECT 10 AS Tag
,2 AS Parent
,NULL AS [DynamicLinksFile]
,NULL AS [DynamicLink]
,NULL AS [ExternalSite]
,NULL AS [ExternalSiteID]
,NULL AS [ExternalSystemId]
,NULL AS [Link]
,NULL AS [Header]
,NULL AS [LinkPage!8!html!CDATA]
,NULL AS [LinkPage!8]
,NULL AS [AssignedValue]
,linkid AS Linkid
,NULL AS [DynamicLinkType]
FROM @TestTable
UNION ALL
SELECT 11 AS Tag
,2 AS Parent
,NULL AS [DynamicLinksFile]
,NULL AS [DynamicLink]
,NULL AS [ExternalSite]
,NULL AS [ExternalSiteID]
,NULL AS [ExternalSystemId]
,NULL AS [Link]
,NULL AS [Header]
,NULL AS [LinkPage!8!html!CDATA]
,NULL AS [LinkPage!8]
,NULL AS [AssignedValue]
,NULL AS [linkid]
,'ExternalLink' AS [DynamicLinkType]
FROM @TestTable
ORDER BY [DynamicLinksFile!1], [DynamicLinks!2]
FOR XML EXPLICIT
Expected result
<DynamicLinksFile>
<DynamicLinks>
<ExternalSite>
<ExternalSiteId>30001</ExternalSiteId>
</ExternalSite>
<Link>Parent</Link>
<Header>30001 header1</Header>
<LinkPage>
<html><![CDATA[my link 1]]></html>
<html><![CDATA[my link 2]]></html>
</LinkPage>
<AssignedValue>3</AssignedValue>
<linkid>30</linkid>
<DynamicLinkType>ExternalLink</DynamicLinkType>
</DynamicLinks>
</DynamicLinksFile>
<DynamicLinksFile>
<DynamicLinks>
<ExternalSite>
<ExternalSiteId>30001</ExternalSiteId>
</ExternalSite>
<Link>Parent</Link>
<Header>30001 header2</Header>
<LinkPage>
<html><![CDATA[my link 3]]></html>
</LinkPage>
<AssignedValue>3</AssignedValue>
<linkid>70</linkid>
<DynamicLinkType>ExternalLink</DynamicLinkType>
</DynamicLinks>
</DynamicLinksFile>
--link_created_date is different
<DynamicLinksFile>
<DynamicLinks>
<ExternalSite>
<ExternalSiteId>30001</ExternalSiteId>
</ExternalSite>
<Link>Parent</Link>
<Header>30001 header2</Header>
<LinkPage>
<html><![CDATA[my link 4]]></html>
</LinkPage>
<AssignedValue>3</AssignedValue>
<linkid>70</linkid>
<DynamicLinkType>ExternalLink</DynamicLinkType>
</DynamicLinks>
</DynamicLinksFile>
<DynamicLinksFile>
<DynamicLinks>
<ExternalSite>
<ExternalSiteId>3002</ExternalSiteId>
</ExternalSite>
<Link>Parent</Link>
<Header>30001 header2</Header>
<LinkPage>
<html><![CDATA[my link 5]]></html>
<html><![CDATA[my link 6]]></html>
</LinkPage>
<AssignedValue>3</AssignedValue>
<linkid>102</linkid>
<DynamicLinkType>ExternalLink</DynamicLinkType>
</DynamicLinks>
</DynamicLinksFile>
August 6, 2018 at 2:42 pm
Do yourself a favour and don't touch XML Explicit with the longest pole you can find. It descends into an unholy, unmaintainable mess in next to no time. Use Path mode instead, it's just better in every conceivable way. You didn't provide a sample of the exact output you want, so I just guessed a bit to show the principle:
Select
siteid,
(
Select
header1,
(
Select
link,
linkid,
link_created_date
From @TestTable TT3
Where TT2.header1= TT3.header1
For XML Path('Link'), Type
)
From @TestTable TT2
Where TT1.siteid = TT2.siteid
Group By header1
For XML Path('Header'), Type
)
From @TestTable TT1
Group By siteid
For XML Path('Site'), Root('Sites')
August 6, 2018 at 2:59 pm
I got it to work. Here are some of the changes that I made.
Here is the solution. You were close for much of it.
DECLARE @TestTable TABLE (siteid int, header1 varchar(255),link varchar(255),linkid varchar(20), link_created_date date)
INSERT INTO @TestTable values (30001,'30001 header1','my link 1',30,GETDATE()-1)
INSERT INTO @TestTable values (30001,'30001 header1','my link 2',30,GETDATE()-1)
INSERT INTO @TestTable values (30001,'30001 header2','my link 3',70,GETDATE()-2)
INSERT INTO @TestTable values (30001,'30001 header2','my link 4',70,GETDATE()-3)
INSERT INTO @TestTable values (30002,'30002 header1','my link 5',102,GETDATE()-1)
INSERT INTO @TestTable values (30002,'30002 header1','my link 6',102,GETDATE()-1)
INSERT INTO @TestTable values (30003,'30003 header1','my link 7',106,GETDATE()-1)
INSERT INTO @TestTable values (30003,'30003 header2','my link 8',106,GETDATE()-1)
;
WITH TestTableCTE AS
(
SELECT *, DENSE_RANK() OVER(ORDER BY siteid, linkid) AS rn
FROM @TestTable
)
SELECT DISTINCT x.*
FROM TestTableCTE t
CROSS APPLY
(
VALUES
(1, NULL, 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, NULL, rn),
(3, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, rn),
(4, 3, NULL, NULL, NULL, t.siteid, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, rn),
--(5, 3, NULL, NULL, NULL, NULL, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, rn),
(6, 2, NULL, NULL, NULL, NULL, NULL, 'Parent', NULL, NULL, NULL, NULL, NULL, NULL, rn),
(7, 2, NULL, NULL, NULL, NULL, NULL, NULL, t.header1, NULL, NULL, NULL, NULL, NULL, rn),
(8, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, rn),
(9, 8, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, t.link, NULL, NULL, NULL, rn),
(10, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 3, NULL, NULL, rn),
(11, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, t.linkid, NULL, rn),
(12, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'ExternalLink', rn),
(13, 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, rn)
) x(
Tag,
Parent,
[DynamicLinksFile!1],
[DynamicLinks!2],
[ExternalSite!3],
[ExternalSiteId!4],
[ExternalSystemId!5],
[Link!6],
[Header!7],
[LinkPage!8],
[html!9!!CDATA],
[AssignedValue!10],
[linkid!11],
[DynamicLinkType!12],
[Sort!13!SortID!Hide]
)
ORDER BY [Sort!13!SortID!Hide], Tag
That being said, I do agree with Andy that EXPLICIT mode should only be used if you need very fine control over the way that your final xml appears. I'll see if I can rewrite this to use PATH instead.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 6, 2018 at 3:07 pm
andycadley - Monday, August 6, 2018 2:42 PMDo yourself a favour and don't touch XML Explicit with the longest pole you can find. It descends into an unholy, unmaintainable mess in next to no time. Use Path mode instead, it's just better in every conceivable way. You didn't provide a sample of the exact output you want, so I just guessed a bit to show the principle:
Select
siteid,
(
Select
header1,
(
Select
link,
linkid,
link_created_date
From @TestTable TT3
Where TT2.header1= TT3.header1
For XML Path('Link'), Type
)
From @TestTable TT2
Where TT1.siteid = TT2.siteid
Group By header1
For XML Path('Header'), Type
)
From @TestTable TT1
Group By siteid
For XML Path('Site'), Root('Sites')
I did a quick check, and FOR XML PATH removes the CDATA tags. The only way to include CDATA in your XML is to use the FOR XML EXPLICIT.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 6, 2018 at 3:17 pm
Oops, yeah I missed the sample at the bottom. If it absolutely has to have CDATA sections, it would appear there really isn't any alternative to EXPLICIT mode.
August 6, 2018 at 5:29 pm
Yes , it needs to have a CDATA section. I would have loved to work with xmlpath instead but CDATA section is the key here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply