Скачать или смотреть How to Query XML Document in SQL Server for Tabular Results

Learn how to effectively query XML documents in SQL Server to retrieve specific data fields and present them in a tabular format.
---
This video is based on the question https://stackoverflow.com/q/77052345/ asked by the user 'Djam75' ( https://stackoverflow.com/u/15052416/ ) and on the answer https://stackoverflow.com/a/77052582/ provided by the user 'siggemannen' ( https://stackoverflow.com/u/13061224/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Query XML document in SQL Server

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Query XML Document in SQL Server for Tabular Results

Working with XML data in SQL Server can sometimes seem daunting, especially when you want to extract specific values in a structured, tabular format. If you’ve found yourself in a similar situation, let’s explore how to efficiently query an XML document to retrieve relevant information.

The Problem

You have an XML document that contains references and their associated values. Specifically, you want to extract the ReferencesValues along with a ParentKey that refers to a key from the Reference node. The XML structure you're working with looks like this:

[[See Video to Reveal this Text or Code Snippet]]

Your goal is to produce a result set that looks like this:

[[See Video to Reveal this Text or Code Snippet]]

The Solution

To achieve this, we can use SQL Server’s XML capabilities. The process involves leveraging the xml datatype and its built-in methods to query XML nodes and attributes.

Step 1: Declare the XML Variable

We start by declaring a variable that holds our XML data:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Extract the Desired Fields

Using the .nodes() method, we can shred our XML into a relational format. For our specific case, we need to select the Key, Code, Label, and the ParentKey. Here's how:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Optimization (Optional)

To streamline the query further, we can use a slightly optimized version that avoids the need to reference parent nodes multiple times:

[[See Video to Reveal this Text or Code Snippet]]

Alternatively, you can traverse the XML in a structured two-step approach:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Extracting specific data from XML documents in SQL Server might appear complex at first, but once you understand how to utilize the XML capabilities, you can efficiently retrieve and display your data in a structured format. With the SQL examples provided, you should be well-equipped to handle similar queries in your projects.

Keep practicing, and soon retrieving data from XML in SQL Server will be second nature!