/*
*    This function gets the XML Interface name ("vendor name") of the external application
*    from XML text that contains a MSS order (as defined in InternalImport.xsd).
*/

create function [dbo].[GetExtAppVendorFromMssXmlText]( @inXmlText Xml )
returns varchar(50)
as
begin
    declare @theApplicationVendor varchar(50)
    declare @theApplicationName varchar(64)

    ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
    select @theApplicationName =
        Interface.value( 'MSS:ExternalSource[1]', 'varchar(64)' )
    from @inXmlText.nodes( '/MSS:Order/MSS:ExternalApplication/MSS:Interface' ) as XmlTable(Interface)

    select @theApplicationVendor =
        VendorName
    from XmlSystemDataTypeMap
    inner join XmlSystem on XmlSystem.XmlSystemID = XmlSystemDataTypeMap.XmlSystemFID
    inner join XmlInterface on XmlInterface.XmlInterfaceID = XmlSystem.XmlInterfaceFID
    where XmlSystemDataTypeMap.Name = @theApplicationName

    return @theApplicationVendor
end
GO
GRANT EXECUTE ON  [dbo].[GetExtAppVendorFromMssXmlText] TO [MssExec]
GO