It's because of the
XMLNS
.
Try following query and should work perfectly.
;WITH XMLNAMESPACES (N'http://www.webserviceX.NET' as X)
SELECT
[Xml_Data].value('(/X:string/X:NewDataSet/X:Table/X:Country)[1]', 'nvarchar(max)') as FirstName
,[Xml_Data].value('(/X:string/X:NewDataSet/X:Table/X:City)[1]', 'nvarchar(max)') as LastName
FROM [AP_DEMO]
Your old query will still work if you just remove the xmlns namespace.
Further reading :
Add Namespaces to Queries with WITH XMLNAMESPACES | Microsoft Docs[
^]
Hope, it helps :)