Need extract XML column data with SQL

  • I have below XML in Table column and need to extract all account details.

    number | type | nickname

    <profile xmlns="http://schemas.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>

  • Sachin Butala-182900 (10/27/2016)


    I have below XML in Table column and need to extract all account details.

    number | type | nickname

    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

  • Awesome, you made my day, Thanks

  • Sachin Butala-182900 (10/27/2016)


    Awesome, you made my day, Thanks

    You are very welcome.

    😎

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply