Reading XML tags through SQL query

See more:

XML

Hi, Below extract from XML file:

<CreditApplication ID="1776065">
<creditapplicationdetails>
<p4lmargin>4.3
<lendermargin>79.3
<p4lmarketing>0
<lendermarketing>0


Equivalent Select Query to read the XML:

SELECT
/*APPLICATION ID*/
CreditApplicationID = ca.value('@ID','INT')
/*APPLICATION DETAILS*/
, P4LMargin = ca.value('(CreditApplicationDetails/P4LMargin/text())[1]','DECIMAL(16,3)')
, LenderMargin = ca.value('(CreditApplicationDetails/LenderMargin/text())[1]','DECIMAL(16,3)')
, P4LMarketing = ca.value('(CreditApplicationDetails/P4LMarketing/text())[1]','BIT')
, LenderMarketing = ca.value('(CreditApplicationDetails/LenderMarketing/text())[1]','BIT')

FROM
AppFileXML tXML
CROSS APPLY tXML.AppFileXMLContent.nodes('MIData/CreditApplication') AS CredAppl(ca)

Now the below two flages :

<p4lmarketing>0
<lendermarketing>0

are changed to :

<Marketing type="CloseBrothersLimited">1
<Marketing type="CloseBrothersPlc">0

What will be the equivalent SQL query for the same? Please help, Thanks in Advance



Thanks & Regards,
Aditya Agarwal

What I have tried:

I am new to XML any help would be highly appreciated