SQl Server XML Select

Posted on 2012-08-15
Last Modified: 2012-09-06
I'm doing a select on some xml

The top line (root) in my xml is
<DocumentDatabase xmlns:xsi="" xmlns:xsd="" xmlns="">

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")] ')=
Question by:lrbrister
    LVL 25

    Accepted Solution

    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 = 
            <b>the value</b>
            <b>other value</b>
    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="" 
            <b>the value</b>
            <b>other value</b>
    SELECT @y.query('declare namespace ns="";  /ns:DocumentDatabase/ns:a')
    ;WITH XMLNAMESPACES ('' as ns)
    SELECT @y.query('/ns:DocumentDatabase/ns:a')
    ;WITH XMLNAMESPACES ('' as ns)
    SELECT col.value('ns:b[1]', 'varchar(max)')
    FROM @y.nodes('//ns:a') tab(col)

    Open in new window


    Author Closing Comment

    Perfect!  Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now