Help on SQL statement to read & extract XML column data

First of all: why parsing SOAP responses in the database and not in the client?

Then: Posting complete XML is necessary, otherwise our samples must be incomplete.

And finally to your question: you're missing the namespaces. And using CROSS APPLY requires the elements to be mandatory. When elements are optional, use OUTER APPLY instead.

E.g.

DECLARE @Data XML = N'
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">
    <s:Header>
        <a:Action s:mustUnderstand="1">http://www.somewebapi.com/services/employeeperson/IEmployeePerson/FindPeopleResponse</a:Action>
    </s:Header>
    <s:Body>
        <FindPeopleResponse xmlns="http://www.somewebapi.com/services/employeeperson">
            <FindPeopleResult xmlns:b="http://www.somewebapi.com/contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <b:OperationResult>
                    <b:HasErrors>false</b:HasErrors>
                    <b:HasWarnings>false</b:HasWarnings>
                    <b:Messages>
                        <b:OperationMessage>
                            <b:Code>OK</b:Code>
                            <b:LogEntryId i:nil="true"/>
                            <b:Message>OK</b:Message>
                            <b:PropertyName i:nil="true"/>
                            <b:Severity>Information</b:Severity>
                        </b:OperationMessage>
                        <b:OperationMessage>
                            <b:Code>OK</b:Code>
                            <b:LogEntryId i:nil="true"/>
                            <b:Message>another Message</b:Message>
                            <b:PropertyName i:nil="true"/>
                            <b:Severity>Information</b:Severity>
                        </b:OperationMessage>
                    </b:Messages>
                </b:OperationResult>
            </FindPeopleResult>              
        </FindPeopleResponse>              
    </s:Body>
</s:Envelope>                   
';

WITH XMLNAMESPACES ( 'http://www.w3.org/2003/05/soap-envelope' AS s ,
                     'http://www.somewebapi.com/contracts' AS b ,
                     DEFAULT 'http://www.somewebapi.com/services/employeeperson' )
SELECT A.OperationResult.value('b:HasErrors[1]', 'NVARCHAR(255)') AS HasErrors ,
       A.OperationResult.value('b:HasWarnings[1]', 'NVARCHAR(255)') AS HasWarnings ,
       OperationMessage.value('b:Code[1]', 'NVARCHAR(255)') AS OperationMessageCode ,
       OperationMessage.value('b:Message[1]', 'NVARCHAR(255)') AS OperationMessageMessage
FROM   @Data.nodes('/s:Envelope/s:Body/FindPeopleResponse/FindPeopleResult/b:OperationResult') A(OperationResult)
       OUTER APPLY OperationResult.nodes('b:Messages/b:OperationMessage') B(OperationMessage);

Select allOpen in new window

p.s. as most XML is Unicode, you should use NVARCHAR instead of VARCHAR.

Thank you Ste5an,

That helped me quite a bit, using the OUTER APPLY and the NVARCHAR.  Using the following script, I do return a row, but it only returns the primaryKey value and everything else is 'NULL'.

I am very novice when it comes to parsing of XML data.  I just wanted to thoroughly understand how to pull information out before doing so from a client.  Do you think I would understand better doing from the client-side instead?

Here is what Returned from the script:

1      NULL      NULL      NULL      NULL      NULL

Here is the SQL script:
SELECT
      upro.primaryKey,
      xmldata.XmlCol.value('(EmployeePerson/CompanyCode)[1]','NVARCHAR(10)') AS CompanyCode,
      xmldata.XmlCol.value('(EmployeePerson/EmployeeNumber)[1]','INT') AS EmployeeNumber,
      xmldata.XmlCol.value('(EmployeePerson/FirstName)[1]','NVARCHAR(50)') AS FirstName,
      xmldata.XmlCol.value('(EmployeePerson/LastName)[1]','NVARCHAR(50)') AS LastName,
      xmldata.XmlCol.value('(EmployeePerson/People/Person/EmailAddress)[1]','VARCHAR(100)') AS EmailAddress
FROM dbo.UproEmpRawXmlData upro
OUTER APPLY upro.XMLData.nodes('/Envelope/Body/FindPeopleResponse/FindPeopleResult/Results') xmldata(XmlCol);

Finally, my apologies for not posting the full XML.  It was really just those two entries that I was testing with.
Here is the XML with the closing tags

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">
    <s:Header>
        <a:Action s:mustUnderstand="1">http://www.somewebapi.com/services/employeeperson/IEmployeePerson/FindPeopleResponse</a:Action>
    </s:Header>
    <s:Body>
        <FindPeopleResponse xmlns="http://www.somewebapi.com/services/employeeperson">
            <FindPeopleResult xmlns:b="http://www.somewebapi.com/contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <b:OperationResult>
                    <b:HasErrors>false</b:HasErrors>
                    <b:HasWarnings>false</b:HasWarnings>
                    <b:Messages>
                        <b:OperationMessage>
                            <b:Code>OK</b:Code>
                            <b:LogEntryId i:nil="true"/>
                            <b:Message>OK</b:Message>
                            <b:PropertyName i:nil="true"/>
                            <b:Severity>Information</b:Severity>
                        </b:OperationMessage>
                    </b:Messages>
                    <b:PagingInfo>
                        <b:CurrentPage>1</b:CurrentPage>
                        <b:PageSize>1000</b:PageSize>
                        <b:PageTotal>8</b:PageTotal>
                        <b:TotalItems>7432</b:TotalItems>
                    </b:PagingInfo>
                    <b:RequestNumber i:nil="true"/>
                    <b:Success>true</b:Success>
                </b:OperationResult>
                <b:Results>
                    <b:EmployeePerson>
                        <b:CompanyCode>WORKS</b:CompanyCode>
                        <b:EmployeeNumber>210547</b:EmployeeNumber>
                        <b:FirstName>Walter</b:FirstName>
                        <b:LastName>Reed</b:LastName>
                        <b:People>
                            <b:Person>
                                <b:AlternateEmailAddress i:nil="true"/>
                                <b:EmailAddress>walter.reed@gmailhospital.com</b:EmailAddress>
                                <b:EmployeeIdentifier i:type="b:EmployeeNumberIdentifier">
                                    <b:CompanyCode>WORKS</b:CompanyCode>
                                    <b:EmployeeNumber>200547</b:EmployeeNumber>
                                </b:EmployeeIdentifier>
                                <b:FirstName>Walter</b:FirstName>
                                <b:FormerLastName i:nil="true"/>
                                <b:LastName>Reed</b:LastName>
                                <b:MiddleName/>
                                <b:PreferredFirstName i:nil="true"/>
                                <b:Prefix i:nil="true"/>
                                <b:SelfServiceProperties i:nil="true" xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/Arrays"/>
                                <b:Suffix i:nil="true"/>
                                <b:SuppressSSN>true</b:SuppressSSN>
                            </b:Person>
                        </b:People>
                    </b:EmployeePerson>
                    <b:EmployeePerson>
                        <b:CompanyCode>WORKS</b:CompanyCode>
                        <b:EmployeeNumber>210586</b:EmployeeNumber>
                        <b:FirstName>John</b:FirstName>
                        <b:LastName>Hopkins</b:LastName>
                        <b:People>
                            <b:Person>
                                <b:AlternateEmailAddress i:nil="true"/>
                                <b:EmailAddress i:nil="true"/>
                                <b:EmployeeIdentifier i:type="b:EmployeeNumberIdentifier">
                                    <b:CompanyCode>WORKS</b:CompanyCode>
                                    <b:EmployeeNumber>210586</b:EmployeeNumber>
                                </b:EmployeeIdentifier>
                                <b:FirstName>John</b:FirstName>
                                <b:FormerLastName i:nil="true"/>
                                <b:LastName>Hopkins</b:LastName>
                                <b:MiddleName/>
                                <b:PreferredFirstName i:nil="true"/>
                                <b:Prefix i:nil="true"/>
                                <b:SelfServiceProperties i:nil="true" xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/Arrays"/>
                                <b:Suffix i:nil="true"/>
                                <b:SuppressSSN>true</b:SuppressSSN>
                            </b:Person>
                        </b:People>
                    </b:EmployeePerson>
                </b:Results>
            </FindPeopleResult>
        </FindPeopleResponse>
    </s:Body>
</s:Envelope>

Hi Ste5an

Thank you so very much for your help.  I truly appreciate your time and patience. This was very helpful.