T-SQL Read xml file with namespaces

Question

I tryng to read xml file in sql server:

DECLARE @XMLToParse  XML 

-- Load the XML data in to a variable to work with.
-- This would typically be passed as a parameter to a stored proc
SET @XMLToParse = '<Response xmlns="http://tempuri.org/">
    <AuthResult xmlns:a="http://schemas.datacontract.org/2004/07/Sistema.Soap.Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <a:Result>
            <a:Id_Value>500</a:Id_Value>
        </a:Result>
        <a:Result>
            <a:Id_Value>895</a:Id_Value>
        </a:Result>
    </AuthResult>
</Response>'

-- Declare temp table to parse data into
DECLARE @ParsingTable  TABLE
    (Id_Value   INT)

-- Parse the XML in to the temp table declared above
INSERT
INTO    @ParsingTable
    (Id_Value)
SELECT  xmlData.A.value('.', 'INT') AS Id_Value
FROM    @XMLToParse.nodes('Response/AuthResult/Result') xmlData(A)

-- Insert into the actual table from the temp table

SELECT  Id_Value
FROM    @ParsingTable

Apparently the code is correct but I can not get the values, where can I be wrong?

Answer 1

Use WITH XMLNAMESPACES to declare the namespaces and use the respective prefix for nodes not in the default namespace.

...

-- Parse the XML in to the temp table declared above
;WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/',
                     'http://schemas.datacontract.org/2004/07/Sistema.Soap.Contracts' as a,
                     'http://www.w3.org/2001/XMLSchema-instance' as i
                     )
INSERT
INTO    @ParsingTable
    (Id_Value)
SELECT  xmlData.A.value('.', 'INT') AS Id_Value
FROM    @XMLToParse.nodes('Response/AuthResult/a:Result') xmlData(A)

...