OPENXML not returning data, no error message

Question

I've never worked with XML before but have been tasked to import a document into a SQL Server table. As a test, I'm been using an example I found online and, for now, I'm only trying to retrieve a single column:

DECLARE @X XML

SELECT @X=MC2016
FROM OPENROWSET (BULK 'C:\TEMP\Detail_2016.xml', SINGLE_BLOB) AS MEDPOP_2016(MC2016)

DECLARE @hdoc int

EXEC SP_XML_PREPAREDOCUMENT @hdoc OUTPUT, @X

select * 
from openxml (@hdoc, '/Results/Record', 1)
with (
    MemberID varchar(10)
    )

EXEC SP_XML_REMOVEDOCUMENT @hdoc

When I execute this in SSMS, the query completes but I get zero rows of output even though I know that there are many thousands of records.

I'm running SQL Server 2016. The XML file is too large include here but here's the first record (deidentified):

<?xml version="1.0" encoding="utf-8"?>
<Results xmlns="http://www.dxcg.com/Results.xsd">
<Record MemberID="0012345" DOB="1917-09-09" Age="100" Gender="M" Child="0" Elderly="1" Male="1" AgeGender="34" NoHCC="false" NoValid="false" NoDiag="false" NoRxGroup="true" NoValidRx="false" ELG1="12" ELIGF1="1" ECAT="-1" MCAID="0" OREC="0" EXP1="33.78" ExpRx1="0.00">
  <Groups>
    <Group Name="FSC" Value="99" />
  </Groups>
  <Period Name="Base">
    <Classifier Type="Standard">
      <DxGs>
        <DxG ID="887" />
        <DxG ID="638" />
        <DxG ID="910" />
        <DxG ID="911" />
        <DxG ID="716" />
        <DxG ID="534" />
        <DxG ID="530" />
        <DxG ID="61" />
      </DxGs>
      <CCs>
        <CC ID="266" HierarchyY1="true" HierarchyY2="true" />
        <CC ID="230" HierarchyY1="true" HierarchyY2="true" />
        <CC ID="19" HierarchyY1="true" HierarchyY2="true" />
        <CC ID="349" HierarchyY1="false" HierarchyY2="true" />
        <CC ID="231" HierarchyY1="false" HierarchyY2="false" />
        <CC ID="347" HierarchyY1="true" HierarchyY2="false" />
        <CC ID="293" HierarchyY1="true" HierarchyY2="true" />
      </CCs>
      <RCCs>
        <RCC ID="77" HierarchyY1="true" HierarchyY2="true" />
        <RCC ID="62" HierarchyY1="true" HierarchyY2="true" />
        <RCC ID="2" HierarchyY1="true" HierarchyY2="true" />
        <RCC ID="103" HierarchyY1="true" HierarchyY2="true" />
        <RCC ID="87" HierarchyY1="true" HierarchyY2="true" />
      </RCCs>
      <ACCs>
        <ACC ID="19" HierarchyY1="true" HierarchyY2="true" />
        <ACC ID="16" HierarchyY1="true" HierarchyY2="true" />
        <ACC ID="2" HierarchyY1="true" HierarchyY2="true" />
        <ACC ID="27" HierarchyY1="true" HierarchyY2="true" />
        <ACC ID="22" HierarchyY1="true" HierarchyY2="true" />
      </ACCs>
      <RxGs />
      <ARxGs />
    </Classifier>
  </Period>
  <DCGs>
    <DCG ID="2" Value="0.5" />
  </DCGs>
  <ADCGs>
    <ADCG ID="2" Value="0.5" />
  </ADCGs>
  <Preds>
    <Pred ID="122" Value="1.6393262810427782" />
    <Pred ID="2" Value="0.55220631651432173">
      <RiskDriver Label="" HCC="293" Contribution="68.537299593489152" />
      <RiskDriver Label="" HCC="230" Contribution="18.964637260918547" />
      <RiskDriver Label="" HCC="347" Contribution="5.12631266098848" />
      <RiskDriver Label="" HCC="266" Contribution="4.43513850335468" />
      <RiskDriver Label="" HCC="19" Contribution="2.936611981249146" />
    </Pred>
  </Preds>
</Record>

Ultimately, I want to load this entire record into a table but as of now, I can't even read a single column and I'm not getting any error messages to point me in a direction to look.

What am I missing here? Thanks.

Best regards, Corey

Answer 1

Starting from the code from the link provided in the comments you can use this code:

DECLARE @X XML

SELECT @X=MC2016
FROM OPENROWSET (BULK 'c:\TEMP\Detail_2016.xml', SINGLE_BLOB) AS MEDPOP_2016(MC2016)

DECLARE @hdoc int

EXEC SP_XML_PREPAREDOCUMENT @hdoc OUTPUT, @X, '<myNs xmlns:ns1="http://www.dxcg.com/Results.xsd"/>'

;WITH XMLNAMESPACES ('http://www.dxcg.com/Results.xsd' as ns1) 
select * 
from openxml (@hdoc, '/ns1:Results/ns1:Record', 1)
with (
    MemberID varchar(10)
    )

Results:

Note: the xml you posted is probably missing a the closing tag for root element </Results>