Check an example:
declare @xml xml = ' <Form> <Elements> <Element ID="12" Text="SectionA" Condition="0"> <Element ID="1" Text="Q1" Condition="4"> </Element> </Element> <Element ID="123" Text="SectionB" Condition="0"> <Element ID="124" Text="Q1" Condition="0"> </Element> </Element> </Elements> </Form> ' select r.value('@ID', 'int') as [Element ID], concat(r.value('../@Text', 'varchar(max)') + ' - ', r.value('@Text', 'varchar(max)')) as [Element Ref], r.value('@Condition', 'varchar(max)') as [Condition], r.value('../@ID', 'int') as [Parent ID] from @xml.nodes('//Element') t(r)
It assumes that Element Ref is a concatenation between parent and child.
Give more details, if any.
0 additional answers
Sort by: Most helpful
SQL XQuery hierarchy help
Very new to XQuery, I have an XML file that is the definition of a form on a mobile app. I need to read the xml to pull out the parent / child relationships where certain criteria is met, and they need to be ordered as they are ordered on the form (so the order in the xml document).
The XML structure is like Form > Elements > Element, but each Element tag could in theory have another nested set of Elements, and there isn't a limit of how many levels it could be. The elements themselves have ID, Text, condition tags within them. For example the first set of Elements might contain an element which is a section of the form. Then There could be a set of questions and then potentially a sub section with more questions and so on.
What I'm ideally after is a table which lists all elements in the order they appear. So the first Element, followed by it's children, and children's children, then the next top level element, followed by it's children and childrens children.
Element ID Element Ref conditions ParentID
12 SectionA 0 NULL
1 SectionA - Q1 4 12
123 SectionB 0 NULL
124 SectionB - Q1 0 123
While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Sign in to comment
Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.