Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQl Server XML Select

Posted on 2012-08-15
2
Medium Priority
?
1,198 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")] ')=
0
Comment
Question by:lrbrister
2 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38300347
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

0
 

Author Closing Comment

by:lrbrister
ID: 38300391
Perfect!  Thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question