Extract all attribute values from XML column in SQL Server

I had to repair your XML, as the provided sample is not well-formed. So you might need to adapt this.

DECLARE @xml XML=
N'<ns2:pay xmlns:ns2="http://someurl.com/">
    <ns2:e k="BID">
        <ns2:l v="2"/>
    </ns2:e>
    <ns2:e k="PMD">
        <ns2:l v="1"/>
    </ns2:e>
    <ns2:e k="GPTA5">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA4">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA3">
        <ns2:s v="572"/>
    </ns2:e>
    <ns2:e k="GPTA1">
        <ns2:s v="Sweet &amp; Sour Sauce"/>
    </ns2:e>
    <ns2:e k="PFID">
        <ns2:l v="1"/>
    </ns2:e>
    <ns2:e k="EAN">
        <ns2:s v="010000"/>
    </ns2:e>
    <ns2:e k="PT">
        <ns2:s v="1"/>
    </ns2:e>
    <ns2:e k="TXID1">
        <ns2:l v="0"/>
    </ns2:e>
    <ns2:e k="PMN">
        <ns2:l v="1"/>
    </ns2:e>
    <ns2:e k="DID">
        <ns2:l v="1"/>
    </ns2:e>
    <ns2:e k="GPTA6">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA7">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="PLU">
        <ns2:l v="10000"/>
    </ns2:e>
    <ns2:e k="GPTA8">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="DYT">
        <ns2:s v="SWEET &amp; SOUR SAUCE"/>
    </ns2:e>
</ns2:pay>';

--This query will return all attributes with their names (a classic key-value-list)

WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT e.value('@k','nvarchar(max)') AS AttributeName
      ,e.value('(ns2:l/@v)[1]','nvarchar(max)') AS AttributeValue
FROM @xml.nodes(N'/ns2:pay/ns2:e') A(e);

--This query allows you to pick the value of one given key

DECLARE @FindThis NVARCHAR(100)='BID';
WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT @xml.value(N'(/ns2:pay/ns2:e[@k=sql:variable("@FindThis")]/ns2:l/@v)[1]','int'); --use the proper type, if all values will be fine with this

--This query will return a table of your values (as long as you knwo all keys in advance)

WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT @xml.value(N'(/ns2:pay/ns2:e[@k="BID"]/ns2:l/@v)[1]','int') AS BID
      ,@xml.value(N'(/ns2:pay/ns2:e[@k="PMD"]/ns2:l/@v)[1]','nvarchar(max)') AS PMD
      ,@xml.value(N'(/ns2:pay/ns2:e[@k="GPTA4"]/ns2:l/@v)[1]','nvarchar(max)') AS GPTA4
--add all keys in the same way...

UPDATE

Here's an example to read this from a table using PIVOT to get it in tabular form:

Hint: I use NVARCHAR(1000) to simulate your need for a cast on-the-fly:

DECLARE @mockupTable TABLE(ID INT,YourData NVARCHAR(1000));
INSERT INTO @mockupTable VALUES
(1
,N'<ns2:pay xmlns:ns2="http://someurl.com/">
    <ns2:e k="BID">
        <ns2:l v="2"/>
    </ns2:e>
    <ns2:e k="PMD">
        <ns2:l v="1"/>
    </ns2:e>
    <ns2:e k="GPTA5">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA4">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA3">
        <ns2:s v="572"/>
    </ns2:e>
    <!--shortened for brevity-->
</ns2:pay>')
,(2
,N'<ns2:pay xmlns:ns2="http://someurl.com/">
    <ns2:e k="BID">
        <ns2:l v="20"/>
    </ns2:e>
    <ns2:e k="PMD">
        <ns2:l v="10"/>
    </ns2:e>
    <ns2:e k="GPTA5">
        <ns2:s v="bla"/>
    </ns2:e>
    <ns2:e k="GPTA4">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA3">
        <ns2:s v="572"/>
    </ns2:e>
    <!--shortened for brevity-->
</ns2:pay>');

--The query will create a key-value list with the row's ID as grouping factor

WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT p.*
FROM
(
    SELECT ID
          ,e.value('@k','nvarchar(max)') AS AttributeName
          ,e.value('(ns2:l/@v)[1]','nvarchar(max)') AS AttributeValue
    FROM @mockupTable t
    --the cast happens here
    CROSS APPLY(SELECT CAST(t.YourData AS XML)) A(TheXml)
    --the call to .nodes() happens here to return a derived table
    CROSS APPLY TheXml.nodes(N'/ns2:pay/ns2:e') B(e)
) tbl
PIVOT(MAX(AttributeValue) 
      FOR AttributeName 
      IN(BID,PMD,GPTA3,GPTA4,GPTA5) --add your columns here, order does not matter
) p