XML Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

XML Help

Author

Topic

Pryach
Starting Member

3 Posts

Posted - 2011-01-29 : 21:16:34

Our schedule software has an API for getting the schedule into an XML format. I'm trying to take this XML file and put it into a SQL 2008 database.

I created an XML source, I did some derived columns with the data, and then have a SQL database output. When I run the package, everything is green, but there's no data being imported into the database. Usually I see a row count, but it's like there's no data at all. It seems the inline scheme, but I'm not sure why it's not seeing the data.

I tried pulling the inline scheme out and putting it into it's own XSD file but got the same result.

Any ideas?


<?xml version="1.0" encoding="utf-16"?>
<DataSet>
  <xs:schema id="Schedule_x0020_Set" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="Schedule_x0020_Set" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="Schedule_x0020_Table">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="CompanyKey" type="xs:string" minOccurs="0" />
                <xs:element name="ScheduleDate" type="xs:string" minOccurs="0" />
                <xs:element name="StaffKey" type="xs:string" minOccurs="0" />
                <xs:element name="StaffID" type="xs:string" minOccurs="0" />
                <xs:element name="StaffLName" type="xs:string" minOccurs="0" />
                <xs:element name="StaffFName" type="xs:string" minOccurs="0" />
                <xs:element name="StaffABBR" type="xs:string" minOccurs="0" />
                <xs:element name="TaskKey" type="xs:string" minOccurs="0" />
                <xs:element name="TaskID" type="xs:string" minOccurs="0" />
                <xs:element name="TaskName" type="xs:string" minOccurs="0" />
                <xs:element name="TaskABBR" type="xs:string" minOccurs="0" />
                <xs:element name="TaskStartTime" type="xs:string" minOccurs="0" />
                <xs:element name="TaskEndTime" type="xs:string" minOccurs="0" />
                <xs:element name="TaskStatCredit" type="xs:string" minOccurs="0" />
                <xs:element name="TaskIsLocked" type="xs:string" minOccurs="0" />
                <xs:element name="TaskIsCredit" type="xs:string" minOccurs="0" />
                <xs:element name="TaskIsStrike" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
    <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <Schedule_x0020_Set>
      <Schedule_x0020_Table diffgr:id="Schedule Table1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
        <CompanyKey>123456</CompanyKey>
        <ScheduleDate>1/24/2011</ScheduleDate>
        <StaffKey>89715891</StaffKey>
        <StaffID />
        <StaffLName>Smith</StaffLName>
        <StaffFName>Josh</StaffFName>
        <StaffABBR>JS</StaffABBR>
        <TaskKey>9018758701</TaskKey>
        <TaskID />
        <TaskName>Day Shift</TaskName>
        <TaskABBR>DS</TaskABBR>
        <TaskStartTime />
        <TaskEndTime />
        <TaskStatCredit>1.000000000</TaskStatCredit>
        <TaskIsLocked>False</TaskIsLocked>
        <TaskIsCredit>True</TaskIsCredit>
        <TaskIsStrike>False</TaskIsStrike>
      </Schedule_x0020_Table>
      <Schedule_x0020_Table diffgr:id="Schedule Table2" msdata:rowOrder="1" diffgr:hasChanges="inserted">
        <CompanyKey>123456</CompanyKey>
        <ScheduleDate>1/24/2011</ScheduleDate>
        <StaffKey>1982375</StaffKey>
        <StaffID />
        <StaffLName>Doe</StaffLName>
        <StaffFName>Jane</StaffFName>
        <StaffABBR>JD</StaffABBR>
        <TaskKey>79182394123</TaskKey>
        <TaskID />
        <TaskName>Night Shift</TaskName>
        <TaskABBR>NS</TaskABBR>
        <TaskStartTime />
        <TaskEndTime />
        <TaskStatCredit>1.000000000</TaskStatCredit>
        <TaskIsLocked>False</TaskIsLocked>
        <TaskIsCredit>True</TaskIsCredit>
        <TaskIsStrike>False</TaskIsStrike>
      </Schedule_x0020_Table>
      <Schedule_x0020_Table diffgr:id="Schedule Table3" msdata:rowOrder="2" diffgr:hasChanges="inserted">
        <CompanyKey>123456</CompanyKey>
        <ScheduleDate>1/24/2011</ScheduleDate>
        <StaffKey>091875897123</StaffKey>
        <StaffID />
        <StaffLName>Johnson</StaffLName>
        <StaffFName>Barbara</StaffFName>
        <StaffABBR>BJ</StaffABBR>
        <TaskKey>1938745890</TaskKey>
        <TaskID />
        <TaskName>Off</TaskName>
        <TaskABBR>OFF</TaskABBR>
        <TaskStartTime />
        <TaskEndTime />
        <TaskStatCredit>1.000000000</TaskStatCredit>
        <TaskIsLocked>False</TaskIsLocked>
        <TaskIsCredit>True</TaskIsCredit>
        <TaskIsStrike>False</TaskIsStrike>
      </Schedule_x0020_Table>
    </Schedule_x0020_Set>
  </diffgr:diffgram>
</DataSet>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources