halp

declare @Object as Int;
declare @Url as varchar(1000) = 'https://www.millerwelds.com/api/OwnersManualSearch/ListUploadedFiles?type=xml';
declare @rt table(response nvarchar(max));


exec sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;
exec sp_OAMethod @Object, 'Open', NULL, 'get', @Url, 'False';
exec sp_OAMethod @Object, 'Send';
insert into @rt(response)
    exec sp_OAGetProperty @Object, 'ResponseText';
exec sp_OADestroy @Object;

/*variables for XML, 1 table, 1 xml variable*/
declare @x xml;
declare @t table(OwnersManualFileViewModel xml);

/*populate variables*/
select @x = (select cast(response as xml) from @rt);
insert @t select cast(response as xml) from @rt;



/*No namespace, all fucky...*/
select @x.query('/ArrayOfOwnersManualFileViewModel/OwnersManualFileViewModel/Name') NameXML;

select
    b.OwnersManual.value('(./Name/text())[1]', 'varchar(500)') OwnersManualName
    , b.OwnersManual.value('(./Url/text())[1]', 'varchar(500)') OwnersManualURL
from @t a
cross apply OwnersManualFileViewModel.nodes('/ArrayOfOwnersManualFileViewModel/OwnersManualFileViewModel') as b(OwnersManual);



/*Wildcard namepsaces*/
select @x.query('/*:ArrayOfOwnersManualFileViewModel/*:OwnersManualFileViewModel/*:Name') NameXML;

select
    b.OwnersManual.value('(./*:Name/text())[1]', 'varchar(500)') OwnersManualName
    , b.OwnersManual.value('(./*:Url/text())[1]', 'varchar(500)') OwnersManualURL
from @t a
cross apply OwnersManualFileViewModel.nodes('/*:ArrayOfOwnersManualFileViewModel/*:OwnersManualFileViewModel') as b(OwnersManual);



/*Define namespace dafuq...*/
with xmlnamespaces(default 'http://schemas.datacontract.org/2004/07/MillerWelds.Website.Models')
    select @x.query('/ArrayOfOwnersManualFileViewModel/OwnersManualFileViewModel/Name') NameXML;

with xmlnamespaces(default 'http://schemas.datacontract.org/2004/07/MillerWelds.Website.Models')
    select
        b.OwnersManual.value('(./Name/text())[1]', 'varchar(500)') OwnersManualName
        , b.OwnersManual.value('(./Url/text())[1]', 'varchar(500)') OwnersManualURL
    from @t a
    cross apply OwnersManualFileViewModel.nodes('/ArrayOfOwnersManualFileViewModel/OwnersManualFileViewModel') as b(OwnersManual);



/*Getting rid of that weird p1 namespace with wildcard namespace...*/
select @x.query('declare default element namespace "http://schemas.datacontract.org/2004/07/MillerWelds.Website.Models";/*:ArrayOfOwnersManualFileViewModel/*:OwnersManualFileViewModel/*:Name') NameXML;

/*Getting rid of that weird p1 namespace with defined namespace...*/
with xmlnamespaces(default 'http://schemas.datacontract.org/2004/07/MillerWelds.Website.Models')
    select @x.query('declare default element namespace "http://schemas.datacontract.org/2004/07/MillerWelds.Website.Models";/ArrayOfOwnersManualFileViewModel/OwnersManualFileViewModel/Name') NameXML;