CODEWITHSUNDEEP
sql-serverxmlsharepointssisxsd
Raymund
Raymund

Reputation: 7892

XML and XSD (Schema) not mapping correctly

Hi guys I need your help on this one, I am trying to import and XML generated by SharePoint to a SQL Server Database. I am doing it by SSIS by using an XML Source to ADO.Net Destination

enter image description here

This is how my XML Looks like (I just included 2 items for this sample)

<data z="#RowsetSchema">
  <z:row ows_LinkTitle="Lorem ipsum" ows_Description="Dolor sit amet, consectetur adipiscing elit. Phasellus commodo turpis quis diam dapibus volutpat. Proin auctor nulla elit, eu aliquam tellus lacinia non. Vestibulum posuere lectus id metus elementum, eget vulputate est lobortis. Morbi eu enim non lectus aliquet feugiat commodo ac diam. Nunc tempor enim leo. Mauris vitae condimentum erat. Vivamus vitae purus justo." ows_Category="Category 1" ows_Amount="150.000000000000" ows_MetaInfo="1;#" ows__ModerationStatus="0" ows__Level="1" ows_Title="Lorem ipsum" ows_ID="1" ows_UniqueId="1;#{32A15E91-0921-496C-8389-B4A861CF63B1}" ows_owshiddenversion="2" ows_FSObjType="1;#0" ows_Created_x0020_Date="1;#2013-11-01 08:59:20" ows_Created="2013-11-01 08:59:20" ows_FileLeafRef="1;#1_.000" ows_PermMask="0x400001f04fff19ff" ows_Modified="2013-11-01 08:59:33" ows_FileRef="1;#sandbox/Lists/Test List/1_.000" xmlns:z="#RowsetSchema" />
  <z:row ows_LinkTitle="Maecenas quis" ows_Description="Felis nec nulla aliquam ullamcorper. Sed et suscipit leo. Morbi mauris nibh, feugiat at commodo eget, tincidunt et eros. Vestibulum convallis ipsum vel laoreet venenatis. Quisque mollis elit sed mattis sollicitudin. Maecenas at pretium nulla. Nullam ultricies tempus lorem ut consequat." ows_Category="Category 2" ows_Amount="89.0000000000000" ows_MetaInfo="2;#" ows__ModerationStatus="0" ows__Level="1" ows_Title="Maecenas quis" ows_ID="2" ows_UniqueId="2;#{AC2914E0-4101-4493-B301-F27EA6F586C1}" ows_owshiddenversion="2" ows_FSObjType="2;#0" ows_Created_x0020_Date="2;#2013-11-01 08:59:27" ows_Created="2013-11-01 08:59:27" ows_FileLeafRef="2;#2_.000" ows_PermMask="0x400001f04fff19ff" ows_Modified="2013-11-01 08:59:46" ows_FileRef="2;#sandbox/Lists/Test List/2_.000" xmlns:z="#RowsetSchema" />
</data>

and this is how my XSD Looks like

<?xml version="1.0" encoding="Windows-1252"?>
<xs:schema xmlns:tns="#RowsetSchema" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="#RowsetSchema" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="data">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="row">
          <xs:complexType>
              <xs:attribute name="ows_LinkTitle" type="xs:string" use="required" />
              <xs:attribute name="ows_Description" type="xs:string" use="required" />
              <xs:attribute name="ows_Category" type="xs:string" use="required" />
              <xs:attribute name="ows_Amount" type="xs:decimal" use="required" />
              <xs:attribute name="ows_MetaInfo" type="xs:string" use="required" />
              <xs:attribute name="ows__ModerationStatus" type="xs:unsignedByte" use="required" />
              <xs:attribute name="ows__Level" type="xs:unsignedByte" use="required" />
              <xs:attribute name="ows_Title" type="xs:string" use="required" />
              <xs:attribute name="ows_ID" type="xs:unsignedByte" use="required" />
              <xs:attribute name="ows_UniqueId" type="xs:string" use="required" />
              <xs:attribute name="ows_owshiddenversion" type="xs:unsignedByte" use="required" />
              <xs:attribute name="ows_FSObjType" type="xs:string" use="required" />
              <xs:attribute name="ows_Created_x0020_Date" type="xs:string" use="required" />
              <xs:attribute name="ows_Created" type="xs:string" use="required" />
              <xs:attribute name="ows_FileLeafRef" type="xs:string" use="required" />
              <xs:attribute name="ows_PermMask" type="xs:string" use="required" />
              <xs:attribute name="ows_Modified" type="xs:string" use="required" />
              <xs:attribute name="ows_FileRef" type="xs:string" use="required" />
            </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Now SSIS recognizes the XSD Correctly as it shows the columns/fields

enter image description here

But that XSD does not map correctly to my XML, what do I need to change on my XSD so it maps correctly? I can also change the XML only to add anything above and below the existing XML.

Upvotes: 0

Views: 1199

Answers (1)

user764357
user764357

Reputation:

The <row> element look valid, the only thing that isn't is the existance of the z attribute on your <data> element.

I'd recommend properly declaring the z namespace on the <data> element like so:

<data xmlns:z="#RowsetSchema">

And removing it from any child elements.

Upvotes: 1

Related Questions