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
– fragmentedreality, Commented Feb 1, 2019 at 6: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
– user10987050, Commented Feb 1, 2019 at 6: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
– fragmentedreality, Commented Feb 1, 2019 at 7:47
I have updated my question with images to give better understanding. — user10987050
– user10987050, Commented Feb 1, 2019 at 15:41

user10987050 · Accepted Answer · 2019-02-11 19:22:06Z

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, 2019 at 19:22

user10987050

752 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parsing XML file with Inline schema using XML Source in SSIS

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related