Clash Royale CLAN TAG#URR8PPP

SQL Hierarchy String Column to XML

Trying to convert a Hierarchy string column in SQL to and XML output

I have this...

HIERARCHY_Column VALUE_Column
Header_1 > Question_A Answer_A
Header_1 > Subheader_1 > Question_B Answer_B
Header_1 > Subheader_2 > Question_C Answer_C
Header_1 > Subheader_2 > Subheader_3 > Question_D Answer_D
Header_1 > Subheader_4 > Subheader_5 > Question_E Answer_E
Header_1 > Subheader_4 > Subheader_5 > Question_F Answer_F
Header_1 > Subheader_6 > Subheader_7 > Subheader_8 > Question_G Answer_G
Header_1 > Subheader_6 > Subheader_7 > Subheader_9 > Question_H Answer_H


I want this output

<Header_1>
<Question_A>Answer_A</Question_A>
<SubHeader_1>
<Question_B>Answer_B</Question_B>
</SubHeader_1>
<SubHeader_2>
<Question_C>Answer_C</Question_C>
<SubHeader_3>
<Question_D>Answer_D</Question_D>
</SubHeader_3>
</SubHeader_2>
<SubHeader_4>
<SubHeader_5>
<Question_E>Answer_E</Question_E>
<Question_F>Answer_F</Question_F>
</SubHeader_5>
</SubHeader_4>
<SubHeader_6>
<SubHeader_7>
<SubHeader_8>
<Question_G>Answer_G</Question_G>
<Question_H>Answer_H</Question_H>
</SubHeader_8>
</SubHeader_7>
</SubHeader_6>
</Header_1>


The data is dynamic so the position and number of the subheaders will change daily per row. So the structure of the above example will change for each Header depending on which questions were answered.

parent_id

1 Answer
1

What about this?

DECLARE @tbl TABLE(ID INT IDENTITY, HierarchyColumn VARCHAR(1000),Content VARCHAR(100));
INSERT INTO @tbl VALUES('Header_1 > Question_A','Answer_A')
,('Header_1 > Subheader_1 > Question_B','Answer_B')
,('Header_1 > Subheader_2 > Question_C','Answer_C')
,('Header_1 > Subheader_2 > Subheader_3 > Question_D','Answer_D')
,('Header_1 > Subheader_4 > Subheader_5 > Question_E','Answer_E')
,('Header_1 > Subheader_4 > Subheader_5 > Question_F','Answer_F')
,('Header_1 > Subheader_6 > Subheader_7 > Subheader_8 > Question_G','Answer_G')
,('Header_1 > Subheader_6 > Subheader_7 > Subheader_9 > Question_H','Answer_H');

DECLARE @stmt VARCHAR(MAX)=' SELECT '
+ STUFF(
(
SELECT ',''' + t.Content + ''' AS ' + QUOTENAME(REPLACE(REPLACE(t.HierarchyColumn,' ',''),'>','/'))
FROM @tbl t
ORDER BY ID
FOR XML PATH('')
),1,1,'') + ' FOR XML PATH('''')';

EXEC( @stmt);


The trick is, to create this statement dynamically and execute it with EXEC():

EXEC()

SELECT 'Answer_A' AS [Header_1/Question_A]
,'Answer_B' AS [Header_1/Subheader_1/Question_B]
,'Answer_C' AS [Header_1/Subheader_2/Question_C]
,'Answer_D' AS [Header_1/Subheader_2/Subheader_3/Question_D]
,'Answer_E' AS [Header_1/Subheader_4/Subheader_5/Question_E]
,'Answer_F' AS [Header_1/Subheader_4/Subheader_5/Question_F]
,'Answer_G' AS [Header_1/Subheader_6/Subheader_7/Subheader_8/Question_G]
,'Answer_H' AS [Header_1/Subheader_6/Subheader_7/Subheader_9/Question_H]
FOR XML PATH('')


The draw back might be, that you must rely on some sort order. I use ID, it might suffice to sort by your hierarchy column (alphanumerically).

ID

Any other approach will get rather tough... Please come back if this does not help you out.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.