March 2008 - Posts

• Grabbing Node Names and Values Pt. 2

  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.   Posted Mar 09 2008, 12:55 AM by Mike C with no comments Filed under: XML, SQL Server, XQuery, function, URI, namespaces

• Grabbing Node Names and Values

  I got a question the other day about grabbing node names and values from an XML document. The person asking was using string parsing with the SUBSTRING function to split out names from content based on '>' characters in the XML string. Not a fullproof solution, to be sure, but quick and easy based on his predictable XML data.  So the question then becomes is there an XML-based method to grab this information?  DECLARE @x xml; SET @x = N'<?xml version = "1.0"?> <root>   <person>     <name>       <first-name>Joe</first-name>       <last-name>Johnson</last-name>     </name>     <name>       <first-name>Lennox</first-name>       <last-name>Lewis</last-name>     </name>     <name>       <first-name>Louie</first-name>       <last-name>McDougal</last-name>     </name>   </person> </root>'; SELECT node.query('fn:local-name(.)') AS NodeName, node.query('./text()') AS NodeValue FROM @x.nodes(N'//*') T(node); The fn:local-name() function retrieves the local name portion of XML node names, and the text() node test retrieves the node text, as shown below.   Posted Mar 07 2008, 11:51 AM by Mike C with no comments Filed under: XML, XQuery, function