As a follow-up to my last post on grabbing node names and values, I was asked about grabbing URIs from more complex XML documents. The following query is the result of that discussion:
DECLARE @x xml;SET @x = N'<?xml version = "1.0"?>
<root xmlns:n = "uri:components:name"
xmlns:p = "uri:components:people">
<p:person>
<name>
<n:first-name>Joe</n:first-name>
<n:last-name>Johnson</n:last-name>
</name>
<name>
<n:first-name>Lennox</n:first-name>
<n:last-name>Lewis</n:last-name>
</name>
<name>
<n:first-name>Louie</n:first-name>
<n:last-name>McDougal</n:last-name>
</name>
</p:person>
</root>';
SELECT CASE NodeUri WHEN '' THEN ''
ELSE '{' + NodeUri + '}' END + NodeName AS [Name],
NodeUri,
NodeName,
NodeValue
FROM
(
SELECT node.value('fn:namespace-uri(.[1])', 'varchar(1000)') AS NodeUri,
node.value('fn:local-name(.[1])', 'varchar(1000)') AS NodeName,
node.query('./text()') AS NodeValue
FROM @x.nodes(N'//*') T(node)
) sub;
The result looks like this:
The first column, Name, represents the fully-expanded URI + local name in the {URI}local-name format. This is what XQuery processors, like the one in SQL Server, see once the namespace prefixes are expanded and prepended to local names. Notice that nodes with no namespace don't have anything prepended. The NodeURI column is generated with a call to the fn:namespace-uri() function, and it represents the URI portion of the namespace. The NodeName and NodeValue columns contain the local name of each node and the value, respectively, just like in the last post.
XQuery supports several functions and operators like these specifically for nodes, and SQL Server supports most of them.
