SQL Hierarchy String Column to XML

The name of the pictureThe name of the pictureThe name of the pictureClash 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.





The solution would be easy if you had a hierarchical entity with a parent_id column or similar.
– The Impaler
Aug 10 at 18:14


parent_id





Are you hoping to do this with only a SQL query?
– Marathon55
Aug 10 at 18:15





Please provide sample data together with your DDL, best as MCVE. There is no easy-cheesy way for unlimited nesting by hierarchy... Provide your samle as consumable table and we will see if there's a hack for you :-)
– Shnugo
Aug 10 at 19:05





Ahhh, I thought you are talking about a real hierarchy column, but looking at your question again I get the feeling, that your data is right as above as chain of strings... Correct?
– Shnugo
Aug 10 at 19:10





@Marathon55 Prefer SQL, but could use SSIS as well
– kentwoodie
Aug 13 at 12:52




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.





this gets me going in the right direction, much appreciated. I do however have characters in the hierarchy column that are invalid for XML identification ie: (, <, =. I'll need to find a workaround that doesn't strip the data too much. Thanks
– kentwoodie
Aug 14 at 15:34






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.

-

Popular posts from this blog

Firebase Auth - with Email and Password - Check user already registered

Image
Clash Royale CLAN TAG #URR8PPP Firebase Auth - with Email and Password - Check user already registered I want to check when a user attempts to signup with createUserWithEmailAndPassword() in Firebase user Authentication method, this user is already registered with my app. createUserWithEmailAndPassword() 8 Answers 8 To detect whether a user with that email address already exists, you can detect when the call to createUserWithEmailAndPassword () fails with auth/email-already-in-use . I see that @Srinivasan just posted an answer for this. createUserWithEmailAndPassword () auth/email-already-in-use Alternatively, you can detect that an email address is already used by calling fetchSignInMethodsForEmail() (previous called fetchProvidersForEmail() ). fetchSignInMethodsForEmail() fetchProvidersForEmail() When the user trying to create an user with same email address, the task response will be "Response: The email address is already in use by another account." mFirebas
Read more

Dynamically update html content plain JS

Image
Clash Royale CLAN TAG #URR8PPP Dynamically update html content plain JS So I have a script bound in my index.html, which literally has to fetch an array of jsons from an api, then dynamically update my page when get response. But as a result I'm getting an as the only update of my page. here is my script fetch(url) .then((resp) => resp.json()) .then((resp) => resp.results.forEach(item => fetch(item.url) .then((response)=> response.json()) .then((response) => pokeArr.push(response)) .catch(console.log) ) ).catch(console.log) const filler = () => if(!pokeArr) return 0 pokeArr.map((i,j)=> return `<tr>$i.name</tr>` ) const pokeindex = () => document.getElementById('a').appendChild(document.createElement(filler())) pokeindex() When I'm consoling it, I can see in the console all the responses I get, so I'm at least doing the fetching part right. What's parberakan ? What's pokeArr ? – T.J. Crowder Aug 8 at 12
Read more

How to determine optimal route across keyboard

Image
Clash Royale CLAN TAG #URR8PPP How to determine optimal route across keyboard I'm trying to automate the typing process in a video game. Players are given an on-screen QWERTY keyboard layout that they can navigate with the control stick (up, down, left, right) in order to select a letter to type. I was wondering if there was a module for Python (or some other resource I could explore) that would help my program find a path across the keyboard as it types each letter in a given message. In other words, if the first letter typed was "A" and the next letter was "B", the module would know that the program should move 1 space down on the keyboard and 4 spaces to the right. Currently, I have the program resetting to the "G" key after each letter is typed (allowing me to use fixed-directions as the program parses each character in the desired message). As you can imagine, this is quite inefficient and defeats the purpose of automating the typing in the fir
Read more