• Quick thought and as g.britton pointed out, the reference has to be exact, try this out for a size

    😎

    DECLARE @testXml XML;

    select @testXml='<invoices>

    <InvoiceNo>13537010*1</InvoiceNo>

    <psship>value_psship1</psship>

    <InvoiceNo>13537010*3</InvoiceNo>

    <psship>value_psship2</psship>

    <InvoiceNo>13537010*4</InvoiceNo>

    <psship>value_psship3</psship>

    <InvoiceNo>13537010*5</InvoiceNo>

    <psship>value_psship4</psship>

    <InvoiceNo>13537010*7</InvoiceNo>

    <psship>value_psship5</psship>

    </invoices>';

    SELECT

    INVOICE.DATA.value('.[1]','VARCHAR(50)')

    FROM @testXml.nodes('/invoices') Tab(Col)

    OUTER APPLY Tab.Col.nodes('InvoiceNo') AS INVOICE(DATA)

    Results

    -----------

    13537010*1

    13537010*3

    13537010*4

    13537010*5

    13537010*7