DECLARE @temp1 XML
DECLARE @exec_gadget_config XML
DECLARE @exec_client_name_index NVARCHAR(500)
DECLARE @exec_client_number_index NVARCHAR(500)

SELECT @temp1 = '<gadget name="EYDocuments">
                    <drawer id="0000000002">
                        <otherColumns>
                            <col id="000000000B" name="Client Name"  />
                            <col id="000000000C" name="Client Number"  />
                        </otherColumns>
                    </drawer>
                </gadget>'

SELECT @exec_client_number_index = (
        SELECT @temp1.value('(//drawer[@id="0000000002"]/otherColumns//col[@name="Client Name"]/@id)[1]', 'NVARCHAR(500)')
        )

SELECT @exec_client_name_index = (
        SELECT @temp1.value('(//drawer[@id="0000000002"]/otherColumns//col[@name="Client Number"]/@id)[1]', 'NVARCHAR(500)')
        )

SELECT @exec_client_number_index, @exec_client_name_index