XML nodes: Turn Repeated Elements Into Rows

I use XML nodes when repeated elements need separate result rows. Each selected node becomes the context for its row. That makes the later value expressions relative to the element they describe.

Select the repeated element

The first document contains two n elements under r. The nodes path selects both elements, so the relational output has two rows for that document. CaseId connects each row to its source.

I keep the path focused on the repeated element rather than its parent. Selecting r would create a different rowset and require another extraction rule. The node selected for each row determines the context from which the subsequent attribute and text paths are evaluated.

WITH Documents AS
(
 SELECT CaseId, CAST(XmlText AS xml) AS XmlDocument
 FROM (VALUES
 (1,N'<r><n id="1">blue</n><n id="2">green</n></r>'),
 (2,N'<r/>'),
 (3,N'<r><n id="3">blue</n><n id="3">blue</n></r>')) v(CaseId,XmlText)
)
SELECT d.CaseId, n.Node.value('(@id)[1]','int') AS ItemId,
       n.Node.value('(text())[1]','nvarchar(20)') AS Label
FROM Documents AS d
CROSS APPLY d.XmlDocument.nodes('/r/n') AS n(Node)
ORDER BY d.CaseId, ItemId, Label;

Name the method rowset

The method returns an unnamed rowset. The query supplies table alias n and column alias Node. The value calls then operate on n.Node. Their paths address its id attribute and its text.

Those short aliases are part of the working query. I’d preserve them when sharing an example, rather than showing a disconnected method call. They make it clear which XML context produces each scalar and how that context relates to the source document.

Read an empty match correctly

The second document contains no n children. Its nodes result is empty, so CROSS APPLY produces no output row for CaseId two. That absence is an expected result of this query.

I’d decide whether a report needs to retain documents without matches. That would require a different relational policy, rather than pretending a node was present. Counting only the four returned rows doesn’t explain how many source documents were supplied or which document produced none.

Preserve duplicate elements

The third document contains two identical n elements. Both become rows, so the expected output retains the duplicate pair. Their attributes and labels happen to match, but they represent two selected source nodes.

I don’t add DISTINCT merely to make the grid shorter. That would change multiplicity and could hide repeated input. If duplicate elements violate the feed contract, validate that rule explicitly and retain enough source context to explain the rejection.

Keep scalar extraction local to the node

Each value call requests one scalar relative to its current node. The attribute becomes int and the text becomes nvarchar(20). These types are explicit and match the small supplied values.

I’d review the allowed width and numeric range before applying this extraction to a real feed. nodes establishes rows, while value performs typed extraction. Neither operation by itself proves that every source element follows the complete business schema.

Compare the full rowset

The ORDER BY makes the four expected output rows easy to compare. The identical duplicate rows remain identical even though their relative order has no visible distinction. The query changes no source document or application data.

I’d retain every tuple, including duplicates, when checking this example on the target instance. A count-only assertion could miss a wrong attribute or a lost label. After the baseline passes, add the application’s actual namespaces and missing-field rules as separate, explicit cases.

Keep every row, duplicates and all, and the result tells the whole story.

A repeated XML element is not one value, it is one row per element.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.