Parsing XML file with Inline schema using XML Source in SSIS

Question

I have a XML file that contain inline schema. I try to use SSIS XML Source. But it does not show the column names.

Selecting XML file

there are no Columns listed here.

I want to transfer XML data to SQL Server.

This is the sample data from the XML file. file name: wcproduction.xml

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="wcproduction">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="api_st_cde" type="sqltypes:smallint" nillable="1" />
          <xsd:element name="api_cnty_cde" type="sqltypes:smallint" nillable="1" />
          <xsd:element name="api_well_idn" type="sqltypes:int" nillable="1" />
          <xsd:element name="pool_idn" type="sqltypes:int" nillable="1" />
          <xsd:element name="prodn_mth" type="sqltypes:smallint" nillable="1" />
          <xsd:element name="prodn_yr" type="sqltypes:int" nillable="1" />
          <xsd:element name="ogrid_cde" type="sqltypes:int" nillable="1" />
          <xsd:element name="prd_knd_cde" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:char" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="2" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="eff_dte" type="sqltypes:datetime" nillable="1" />
          <xsd:element name="amend_ind" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:char" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="1" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="c115_wc_stat_cde" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:char" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="1" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="prod_amt" type="sqltypes:int" nillable="1" />
          <xsd:element name="prodn_day_num" type="sqltypes:smallint" nillable="1" />
          <xsd:element name="mod_dte" type="sqltypes:datetime" nillable="1" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <wcproduction xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <api_st_cde>30</api_st_cde>
    <api_cnty_cde>5</api_cnty_cde>
    <api_well_idn>20178</api_well_idn>
    <pool_idn>10540</pool_idn>
    <prodn_mth>7</prodn_mth>
    <prodn_yr>1973</prodn_yr>
    <ogrid_cde>12437</ogrid_cde>
    <prd_knd_cde>G </prd_knd_cde>
    <eff_dte>1973-07-31T00:00:00</eff_dte>
    <amend_ind>N</amend_ind>
    <c115_wc_stat_cde>F</c115_wc_stat_cde>
    <prod_amt>53612</prod_amt>
    <prodn_day_num>99</prodn_day_num>
    <mod_dte>2015-04-07T07:31:00.173</mod_dte>
  </wcproduction>
</root>

I want to transfer this xml data from this xml file to database

Answer: XML Source reader task does not handle multiple namespace. we should try this solution. Perhaps we should create Source script component. Reading large XML file using XMLReader in VB.net

what did you already try to achieve your goal? Currently your question is really broad – close to be an open statement. Can you please elaborate on the steps you already took and what went wrong/unexpected? — fragmentedreality, Feb 01 '19 at 06:15
I tried to configure this xml file in SSIS XML Source task with inline schema property set to true. Even-though all settings are configured properly, it does not show the input columns. So, i am struck in this stage. — user10987050, Feb 01 '19 at 06:39
Cool. Please update your answer so that it is visible what steps you took and what failed or was different than expected. I am not a subject expert but just helping you to clarify you question so anyone with knowledge on that matter has all information needed at hand. — fragmentedreality, Feb 01 '19 at 07:47
I have updated my question with images to give better understanding. — user10987050, Feb 01 '19 at 15:41
Congrats on finding out. It is perfectly fine to not just edit your question, but actually post an answer to your own question. That way other people can clearly see, that the question was answered. — fragmentedreality, Feb 06 '19 at 21:01
@user10987050 you have to write this as an answer, not mention it in the question, for more information read the [Tour page](https://www.stackoverflow.com/tour) — Hadi, Feb 10 '19 at 16:22

score 2 · Accepted Answer · answered Feb 11 '19 at 19:22

2

XML Source reader task in SSIS does not handle multiple namespace. we should try this solution. Perhaps we should create Source script component. Reading large XML file using XMLReader in VB.net

Reading large XML file using XMLReader in VB.net

answered Feb 11 '19 at 19:22

user10987050

75
10

Parsing XML file with Inline schema using XML Source in SSIS

1 Answers1