We help IT Professionals succeed at work.

SQl Server XML Select

Medium Priority
1,316 Views
Last Modified: 2012-09-06
I'm doing a select on some xml

The top line (root) in my xml is
<DocumentDatabase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://someone.net/services/subscribers">

When I do this select it ONLY WORKS if I change the root to <DocumentDatabase>

How can I leave it as is and make my query work?

SELECT @XMLString.query('DocumentDatabase') [Query]

SELECT tab.col.value('SignerProvidedValue[1]','VARCHAR(MAX)') PermAddress
FROM @XMLString.nodes('//FormFields/FormField') tab(col)
WHERE  tab.col.exist('Name/text()[.= sql:variable("@cLookup")] ')=
Comment
Watch Question

Project Architect
CERTIFIED EXPERT
Commented:
It is the namespace that is causing the problems here.  Have look at, and even run, the following:
-- without namespaces
DECLARE @x xml
SET @x = 
'<DocumentDatabase>
    <a>
        <b>the value</b>
        <b>other value</b>
    </a>
 </DocumentDatabase>'

SELECT @x.query('/DocumentDatabase/a')

SELECT col.value('b[1]', 'varchar(max)')
FROM @x.nodes('//a') tab(col)

-- with namespaces
DECLARE @y xml
SET @y = 
'<DocumentDatabase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                   xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                   xmlns="http://someone.net/services/subscribers">
    <a>
        <b>the value</b>
        <b>other value</b>
    </a>
 </DocumentDatabase>'

SELECT @y.query('declare namespace ns="http://someone.net/services/subscribers";  /ns:DocumentDatabase/ns:a')

;WITH XMLNAMESPACES ('http://someone.net/services/subscribers' as ns)
SELECT @y.query('/ns:DocumentDatabase/ns:a')

;WITH XMLNAMESPACES ('http://someone.net/services/subscribers' as ns)
SELECT col.value('ns:b[1]', 'varchar(max)')
FROM @y.nodes('//ns:a') tab(col)

Open in new window

Larry Bristersr. Developer

Author

Commented:
Perfect!  Thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.