Need extract XML column data with SQL

• Sachin Butala-182900

  SSC-Addicted

  Points: 406

  More actions

  October 27, 2016 at 2:20 am

  #104382

  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>

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  October 27, 2016 at 2:56 am

  #1909060

  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

• Sachin Butala-182900

  SSC-Addicted

  Points: 406

  More actions

  October 27, 2016 at 4:00 am

  #1909066

  Awesome, you made my day, Thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  October 27, 2016 at 4:35 am

  #1909074

  Sachin Butala-182900 (10/27/2016)

  ---

  Awesome, you made my day, Thanks

  You are very welcome.

  😎