SQL Server 2016 XML Shredding

Have been trying to figure this out for a while without success, read like 10 posts and some other examples and the MS help, not resonating, need to shred some xml data with the following format:

<ncf_report xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://cp.com/rules/client">
  <admin>
    <quoteback name="abcd">ABCD A</quoteback>
    <product_group>Abcd ABcd Abcd</product_group>
    <pnc_account>123456</pnc_account>
    <pnc_account_name>ABC</pnc_account_name>
    <product_reference>123456789</product_reference>
    <report_type>ABCDE</report_type>
    <status>ABCDE</status>
    <ownership>ABCD</ownership>
    <report_code>1234</report_code>
    <report_description>Abcde/report_description>
    <purpose>ABCDEFGH</purpose>
    <date_request_ordered>05/05/2020</date_request_ordered>
    <date_request_received>05/05/2020</date_request_received>
    <date_request_completed>05/05/2020</date_request_completed>
    <time_report_processed>1028</time_report_processed>
    <multiple_scores_ordered>false</multiple_scores_ordered>
    <vendor name="Abcd" address="Abcd" />
    <report>
      <sequence>0000000001</sequence>
      <count>0000000001</count>
    </report>
  </admin>
  <report>
    <alerts_scoring>
      <scoring>
        <score status="Abcd">
          <model_label>ABCD</model_label>
          <score>123</score>
          <rating_state>AB</rating_state>
          <classification> ABCD </classification>
          <reason_codes>
            <code>12</code>
            <description>ABCD</description>
          </reason_codes>
          <reason_codes>
            <code>12</code>
            <description>ABCD</description>
          </reason_codes>
          <reason_codes>
            <code>12</code>
            <description>ABCD ABCD ABCD</description>
          </reason_codes>
          <reason_codes>
            <code>12</code>
            <description>ABCD ABCD ABCD</description>
          </reason_codes>
        </score>
      </scoring>
      <general>ABCD ABCD ABCD ORIGINAL REPORT DATE: 12/12/2000</general>
      <general>ABCD ABCD ABCD</general>
      <general> ABCD ABCD ABCD</general>
      <general narrativeCode="Abcd Abcd">ABCD ABCD ABCD</general>
      <general narrativeCode=" Abcd Abcd">ABCD ABCD ABCD</general>
      <general narrativeCode=" Abcd Abcd">ABCD ABCD ABCD</general>
    </alerts_scoring>
    <vendor_dataset>
      <subjects>
        <subject type="Abcd" relationship_to_data="Abcd">
          <name type="Abcd">
            <first>XXXX</first>
            <middle>X</middle>
            <last>XXXX</last>
          </name>
          <birth_date>01/01/1900</birth_date>
          <ssn>999999999</ssn>
          <address type="Abcd" ref="1" />
          <address type="Abcd" ref="2" />
          <address type="Abcd" ref="3" />
        </subject>
      </subjects>
      <addresses>
        <address id="1">
          <street1>ABCD</street1>
          <city>ABCD</city>
          <state>AB</state>
          <postalcode>12345</postalcode>
          <zip4>1234</zip4>
          <date_first_at_address>01/02/1900</date_first_at_address>
          <date_last_at_address>01/02/1900</date_last_at_address>
        </address>
        <address id="2">
          <house>123</house>
          <street1>ABCDE</street1>
          <city>ABCDE</city>
          <state>AB</state>
          <postalcode>12345</postalcode>
          <zip4>1234</zip4>
          <date_first_at_address>00/00/1900</date_first_at_address>
          <date_last_at_address>00/00/1900</date_last_at_address>
        </address>
        <address id="3">
          <street1>ABCDE</street1>
          <city>ABCDE</city>
          <state>AB</state>
          <postalcode>12345</postalcode>
          <zip4>1234</zip4>
          <date_first_at_address>00/00/1900</date_first_at_address>
          <date_last_at_address>00/00/1900</date_last_at_address>
        </address>
      </addresses>
    </vendor_dataset>
    <summary>
      <date_oldest_trade>00/00/1900</date_oldest_trade>
      <date_latest_trade>00/00/1900</date_latest_trade>
      <date_latest_activity>00/00/1900</date_latest_activity>
      <includes_bankruptcies flag="true" date="02/02/2009" />
      <includes_other_records public_records="false" collection="true" consumer_statement="false" />
      <credit_range high="123456" low="1234" number_trade_lines="12" />
      **<account_status_counters>
        <account type="current" description="Pays Account as Agreed" status="1">12</account>
        <account type="current" description="Status Not Known" status=" ">7</account>
        <account type="former" description="Pays/Paid 30-60 Days or Max 2 Payments Past Due" status="2">5</account>
        <account type="former" description="Pays/Paid 60-90 Days or Max 3 Payments Past Due" status="3">4</account>
        <account type="former" description="Bad Debt" status="9">6</account>
      </account_status_counters>**

I currently going down the path of trying to use the xml procedure but I could not get to the finish line with openxml as well. Trying to extract data in highlighted at bottom of xml

EXEC sp_xml_preparedocument @hdoc OUTPUT, @CreditScoreXML 
SELECT * FROM OPENXML(@hdoc, '/<ncf_report xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://cp.com/rules/client">/admin/summary/account_status_counters')
WITH
(
  [Ref_Number] VARCHAR(10) 'product_reference',
  [current_account_type] VARCHAR(10) './account/@type',
  [current_account_type_description] VARCHAR(50) './account/@description',
  [current_account_type_description] VARCHAR(1) './account/@status'

Solution

You can define the namespace for your XML using WITH XMLNAMESPACES statement, then you can extract the values you need with .value().

I don't understand exactly the information you are trying to extract, but this should put you on the right track (I only put the first row of your xml to save space, you should put the entire XML fragment in the @xml variable):

declare @xml xml set @xml='
    <ncf_report xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://cp.com/rules/client">
    ...
'

;WITH XMLNAMESPACES ('http://cp.com/rules/client' as ns1)
select 
 @xml.value('(ns1:ncf_report/ns1:admin/ns1:product_reference)[1]', 'varchar(10)') as Ref_Number
,@xml.value('(ns1:ncf_report/ns1:report/ns1:summary/ns1:account_status_counters/ns1:account[@type="current" and @status ="1"]/@description)[1]', 'varchar(50)') as CurrentDescription
,@xml.value('(ns1:ncf_report/ns1:report/ns1:summary/ns1:account_status_counters/ns1:account[@type="current" and @status ="1"])[1]', 'int') as CurrentStatus
,@xml.value('(ns1:ncf_report/ns1:report/ns1:summary/ns1:account_status_counters/ns1:account[@type="current" and @status =" "]/@description)[1]', 'varchar(50)') as CurrentDescription_2
,@xml.value('(ns1:ncf_report/ns1:report/ns1:summary/ns1:account_status_counters/ns1:account[@type="current" and @status =" "])[1]', 'int') as CurrentStatus_2

This sample query would extract: