Can't read a property value from XML-CodePudding

I'm trying to get the value from the property Success but I can't, I don't know where I'm wrong.

This is my code

DECLARE @Response VARCHAR(8000) = '<?xml version="1.0" encoding="utf-8"?>  
<Result xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/">    
    <Success>true</Success>  
</Result>'

DECLARE @xml TABLE (
    Content XML
)

INSERT INTO @xml
SELECT CAST(@Response AS XML)

SELECT
    Content.value('(/Result/Success)[1]', 'BIT')
FROM @xml

The property Success is bool type

I'm trying with different scope types (nvarchar, varchar, bit, etc..)

This is what I expecting

CodePudding user response：

Please try the following solution.

Notable points:

It is always better to use XML data type instead of the VARCHAR(..) for XML data.
All XML elements are bound to the default namespace even if we don't see it explicitly. That's why we need to specify it via XMLNAMESPACES clause.
It is always better to use text() in the XPath expressions for XML elements for performance reasons. Peculiarity of the MS SQL Server.
It is possible to omit XML prolog declaration completely. SQL Server doesn't store it.

SQL

DECLARE @Response XML = '<?xml version="1.0" encoding="utf-8"?>  
<Result xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/">    
    <Success>true</Success>  
</Result>';

DECLARE @xml TABLE (Content XML);

INSERT INTO @xml
SELECT @Response;

;WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/')
SELECT result = Content.value('(/Result/Success/text())[1]', 'BIT')
FROM @xml;

Output

result
1