SQL Server XML multi-level query

sql server xml query multiple nodes
sql server nested xml example
sql server xml select child nodes
convert xml column to table sql server
export xml data from sql server
how to insert xml data into table in sql server
sql server xml list all nodes
sql server for xml path

I have the following xml that I need to query:

declare @xml xml = '<root>
    <level1>
        <property1>Value1</property1>
        <property2>Value2</property2>
        <level2List>
            <level2>Child1</level2>
            <level2>Child2</level2>
        </level2List>
    </level1>
    <level1>
        <property1>Value3</property1>
        <property2>Value4</property2>
        <level2List>
            <level2>Child3</level2>
            <level2>Child4</level2>
        </level2List>
    </level1>
</root>'

And I need the following result:

Property1 Property2 Child
Value1    Value2    Child1
Value1    Value2    Child2
Value3    Value4    Child3
Value3    Value4    Child4

I have this query:

select col.value('Property1','varchar(100)') Property1,
       col.value('Property2','varchar(100)') Property2
from @xml.nodes('//root/level1') as tab(col)

But I can't figure out how to join a parent node with its children. Any idea?

You can start searching from the bottom:

DECLARE @xml AS XML = '<root>
    <level1>
        <property1>Value1</property1>
        <property2>Value2</property2>
        <level2List>
            <level2>Child1</level2>
            <level2>Child2</level2>
        </level2List>
    </level1>
    <level1>
        <property1>Value3</property1>
        <property2>Value4</property2>
        <level2List>
            <level2>Child3</level2>
            <level2>Child4</level2>
        </level2List>
    </level1>
</root>';

SELECT
    n.value('../../property1[1]','varchar(100)') property1,
    n.value('../../property2[1]','varchar(100)') property2,
    n.value('.','varchar(100)') Child
FROM @xml.nodes('//level1//level2') AS x(n)

Or by extending your original attempt:

FROM @xml.nodes('//level1') AS n1(l1)
CROSS APPLY l1.nodes('.//level2') AS n2(l2)

Parse XML with multilevel nesting in SQL, Assuming you have your XML in a SQL Server variable called @XML , you can use the native XQuery support in SQL Server 2005 and newer  The xml data type and the TYPE directive in FOR XML queries enable the XML returned by the FOR XML queries to be processed on the server as well as on the client. Processing with xml Type Variables You can assign the FOR XML query result to an xml type variable, or use XQuery to query the result, and assign that result to an xml type variable for more processing.

I would use following:

SELECT
    X.value('(../../property1)[1]', 'varchar(20)') Property1,
    X.value('(../../property2)[1]', 'varchar(20)') Property2,
    X.value('.', 'varchar(20)') Child
FROM @xml.nodes('//level2') N(X)

QuerySurge Support for XML with Multilevel Hierarchy – Customer , QuerySurge Support for multilevel XML Hierarchy First, in the <table> tag, the name attribute designates the name you will use in your SQL to query your XML. Querying SQL Server Data with XQuery XQuery is a language designed for querying XML data, and is not proprietary to SQL Server - it is used by many relational database management systems (RDBMS). A simple implementation of an XQuery solution is as follows:

You can also use APPLY :

SELECT col.value('property1[1]', 'varchar(100)') AS property1,
       col.value('property2[1]', 'varchar(100)') AS property2,
       col1.value('text()[1]','varchar(100)') AS Child
FROM @xml.nodes('root/level1') AS tab(col) OUTER APPLY 
     col.nodes('level2List/level2') tab1(col1);

Working with XML Data in SQL Server, In this article, we will see how we can work with XML in SQL Server. document in a new query window of SQL Server management studio: record is an element and each column is a nested element for a particular record. The result is of xml type. The method returns an instance of untyped XML. Syntax query ('XQuery') Arguments. XQuery Is a string, an XQuery expression, that queries for XML nodes, such as elements and attributes, in an XML instance. Examples. This section provides examples of using the query() method of the xml data type. A.

FOR XML PATH clause in SQL Server, We use the FOR XML PATH SQL Statement to concatenate multiple Click on the Result to Text in the SSMS toolbar and rerun the query. Converting into XML from SQL tables. The simplest way to convert data from SQL tables into XML format is to use the FOR XML AUTO and FOR XML PATH clauses. FOR XML AUTO in SQL SERVER. The FOR XML AUTO clause converts each column in the SQL table into an attribute in the corresponding XML document. Execute the following script:

Manipulating XML Data in SQL Server, SQL Server provides plenty of XML-related tools, but how can we know Note that the XML namespace data is included in the nested node. expand the size of our query – since we are returning four XML node values, we  XQuery is a query language for XML. XML is a markup language that provides software and hardware-independent way of storing, transporting, and sharing data. In this case, XML documents are stored in a SQL Server database. XQuery is used to manipulate, find and extract information stored in the XML format.

Using the FOR XML Clause to Return Query Results as XML , The FOR XML clause in SQL Server causes a lot of difficulty, mainly because it is SELECT statements, if those statements define the outer, or top-level, query. the column is nested at the deepest level wherever it appears. When the average database developer is obliged to manipulate XML, either shredding it into relational format, or creating it from SQL, it is often done 'at arms length'. A shame, since effective use of techniques that go beyond the basics can save much code, and are likely to perform better.

Comments
  • I don't think your XML is formatted that way. According to this example, level2List, Property1, and property2 are sister nodes under the Level node.
  • Good +1, trying to notice differences between this and mine. The biggest is x(n) instead of N(X) :)