Quick solution

😎

DECLARE @TXML XML = '<profile xmlns="http://schemas.clairmail.com/2008/01/Model/extensions" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<disabledState>

<isDisabledIndicator>true</isDisabledIndicator>

<disabledDateTime>2012-12-02T07:44:14.640</disabledDateTime>

</disabledState>

<registeredAccounts>

<account>

<accountId>

<number>031111111111111</number>

<type>CC</type>

<companyId>PG</companyId>

</accountId>

<isPrimary>false</isPrimary>

<nickname>CC2720</nickname>

<transferFromIndicator>true</transferFromIndicator>

<transferToIndicator>true</transferToIndicator>

<searchableIndicator>true</searchableIndicator>

<billPayIndicator>true</billPayIndicator>

<rdcIndicator>false</rdcIndicator>

<displayAccountIndicator>true</displayAccountIndicator>

<displayTransactionsIndicator>true</displayTransactionsIndicator>

<eligibleForAlertRegistration>true</eligibleForAlertRegistration>

<p2pFromIndicator>true</p2pFromIndicator>

<p2pToIndicator>true</p2pToIndicator>

<properties>

<property>

<key>displayName</key>

<value>CC</value>

</property>

</properties>

<properties>

<property>

<key>displayName</key>

<value xsi:nil="true" />

</property>

</properties>

</account>

<account>

<accountId>

<number>0411111111111</number>

<type>AHO</type>

<companyId>PG</companyId>

</accountId>

<isPrimary>true</isPrimary>

<nickname>AHO8385</nickname>

<transferFromIndicator>true</transferFromIndicator>

<transferToIndicator>true</transferToIndicator>

<searchableIndicator>true</searchableIndicator>

<billPayIndicator>true</billPayIndicator>

<rdcIndicator>false</rdcIndicator>

<displayAccountIndicator>true</displayAccountIndicator>

<displayTransactionsIndicator>true</displayTransactionsIndicator>

<eligibleForAlertRegistration>true</eligibleForAlertRegistration>

<p2pFromIndicator>true</p2pFromIndicator>

<p2pToIndicator>true</p2pToIndicator>

<properties>

<property>

<key>displayName</key>

<value>CDA</value>

</property>

</properties>

<properties>

<property>

<key>displayName</key>

<value xsi:nil="true" />

</property>

</properties>

</account>

<account>

<accountId>

<number>0411111116497</number>

<type>AHO</type>

<companyId>PG</companyId>

</accountId>

<isPrimary>false</isPrimary>

<nickname>AHO6497</nickname>

<transferFromIndicator>true</transferFromIndicator>

<transferToIndicator>true</transferToIndicator>

<searchableIndicator>true</searchableIndicator>

<billPayIndicator>true</billPayIndicator>

<rdcIndicator>false</rdcIndicator>

<displayAccountIndicator>true</displayAccountIndicator>

<displayTransactionsIndicator>true</displayTransactionsIndicator>

<eligibleForAlertRegistration>true</eligibleForAlertRegistration>

<p2pFromIndicator>true</p2pFromIndicator>

<p2pToIndicator>true</p2pToIndicator>

<properties>

<property>

<key>displayName</key>

<value>CDA</value>

</property>

</properties>

<properties>

<property>

<key>displayName</key>

<value xsi:nil="true" />

</property>

</properties>

</account>

<account>

<accountId>

<number>0411111110900</number>

<type>AHO</type>

<companyId>PG</companyId>

</accountId>

<isPrimary>false</isPrimary>

<nickname>AHO0900</nickname>

<transferFromIndicator>true</transferFromIndicator>

<transferToIndicator>true</transferToIndicator>

<searchableIndicator>true</searchableIndicator>

<billPayIndicator>true</billPayIndicator>

<rdcIndicator>false</rdcIndicator>

<displayAccountIndicator>true</displayAccountIndicator>

<displayTransactionsIndicator>true</displayTransactionsIndicator>

<eligibleForAlertRegistration>true</eligibleForAlertRegistration>

<p2pFromIndicator>true</p2pFromIndicator>

<p2pToIndicator>true</p2pToIndicator>

<properties>

<property>

<key>displayName</key>

<value>CDA</value>

</property>

</properties>

<properties>

<property>

<key>displayName</key>

<value xsi:nil="true" />

</property>

</properties>

</account>

<account>

<accountId>

<number>0411111110900000003</number>

<type>TC</type>

<companyId>PG</companyId>

</accountId>

<isPrimary>false</isPrimary>

<nickname>TC0003</nickname>

<transferFromIndicator>false</transferFromIndicator>

<transferToIndicator>true</transferToIndicator>

<searchableIndicator>true</searchableIndicator>

<billPayIndicator>false</billPayIndicator>

<rdcIndicator>false</rdcIndicator>

<displayAccountIndicator>true</displayAccountIndicator>

<displayTransactionsIndicator>true</displayTransactionsIndicator>

<eligibleForAlertRegistration>true</eligibleForAlertRegistration>

<p2pFromIndicator>true</p2pFromIndicator>

<p2pToIndicator>true</p2pToIndicator>

<properties>

<property>

<key>displayName</key>

<value>VBGP</value>

</property>

</properties>

<properties>

<property>

<key>displayName</key>

<value xsi:nil="true" />

</property>

</properties>

</account>

</registeredAccounts>

</profile>';

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.clairmail.com/2008/01/Model/extensions')

SELECT

--- Account

ACCOUNT.DATA.value('(accountId/number/text())[1]' ,'VARCHAR(25)') AS accountId_number

,ACCOUNT.DATA.value('(accountId/type/text())[1]' ,'VARCHAR(10)') AS accountId_type

,ACCOUNT.DATA.value('(accountId/companyId/text())[1]' ,'VARCHAR(10)') AS accountId_companyId

,ACCOUNT.DATA.value('(isPrimary/text())[1]' ,'VARCHAR(25)') AS isPrimary

,ACCOUNT.DATA.value('(nickname/text())[1]' ,'VARCHAR(25)') AS nickname

,ACCOUNT.DATA.value('(transferFromIndicator/text())[1]' ,'VARCHAR(25)') AS transferFromIndicator

,ACCOUNT.DATA.value('(transferToIndicator/text())[1]' ,'VARCHAR(25)') AS transferToIndicator

,ACCOUNT.DATA.value('(searchableIndicator/text())[1]' ,'VARCHAR(25)') AS searchableIndicator

,ACCOUNT.DATA.value('(billPayIndicator/text())[1]' ,'VARCHAR(25)') AS billPayIndicator

,ACCOUNT.DATA.value('(rdcIndicator/text())[1]' ,'VARCHAR(25)') AS rdcIndicator

,ACCOUNT.DATA.value('(displayAccountIndicator/text())[1]' ,'VARCHAR(25)') AS displayAccountIndicator

,ACCOUNT.DATA.value('(displayTransactionsIndicator/text())[1]' ,'VARCHAR(25)') AS displayTransactionsIndicator

,ACCOUNT.DATA.value('(eligibleForAlertRegistration/text())[1]' ,'VARCHAR(25)') AS eligibleForAlertRegistration

,ACCOUNT.DATA.value('(p2pFromIndicator/text())[1]' ,'VARCHAR(25)') AS p2pFromIndicator

,ACCOUNT.DATA.value('(p2pToIndicator/text())[1]' ,'VARCHAR(25)') AS p2pToIndicator

--- Properties

,ACCOUNT.DATA.value('(properties/property/key/text())[1]' ,'VARCHAR(25)') AS Prop_1_key

,ACCOUNT.DATA.value('(properties/property/value/text())[1]' ,'VARCHAR(25)') AS Prop_1_value

,ACCOUNT.DATA.value('(properties/property/key/text())[2]' ,'VARCHAR(25)') AS Prop_2_key

,ACCOUNT.DATA.value('(properties/property/value/text())[2]' ,'VARCHAR(25)') AS Prop_2_value

FROM @TXML.nodes('profile/registeredAccounts/account') ACCOUNT(DATA);

Output

accountId_number accountId_type accountId_companyId isPrimary nickname transferFromIndicator transferToIndicator searchableIndicator billPayIndicator rdcIndicator displayAccountIndicator displayTransactionsIndicator eligibleForAlertRegistration p2pFromIndicator p2pToIndicator Prop_1_key Prop_1_value Prop_2_key Prop_2_value

------------------------- -------------- ------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ---------------------------- ---------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------

031111111111111 CC PG false CC2720 true true true true false true true true true true displayName CC displayName NULL

0411111111111 AHO PG true AHO8385 true true true true false true true true true true displayName CDA displayName NULL

0411111116497 AHO PG false AHO6497 true true true true false true true true true true displayName CDA displayName NULL

0411111110900 AHO PG false AHO0900 true true true true false true true true true true displayName CDA displayName NULL

0411111110900000003 TC PG false TC0003 false true true false false true true true true true displayName VBGP displayName NULL