XML Source giving blues while fetching data

Question

Hi All,

I have a webservice from which i need to populate my database tables.

As of now i have used a webservice task to fetch data from web service in an XMl file.

But this XML has Multiple Namespaces which is not supported by SSIS (Supports only single namespace).

So to overcome this issue i used XML task with opeartion type as "XSLT" And used following code in XSLT file:

<?xml version="1.0" encoding="utf-8" ?> <xsl:stylesheet version="1.0"         xmlns:xsl="http://www.w3.org/1999/XSL/Transform">   <xsl:output method="xml" indent="no" />   <xsl:template match="/|comment()|processing-instruction()">     <xsl:copy>       <xsl:apply-templates />     </xsl:copy>   </xsl:template>   <xsl:template match="*">     <xsl:element name="{local-name()}">       <xsl:apply-templates select="@*|node()" />     </xsl:element>   </xsl:template>   <xsl:template match="@*">     <xsl:attribute name="{local-name()}">       <xsl:value-of select="." />     </xsl:attribute>   </xsl:template> </xsl:stylesheet>

 

Using this i am able to generate a new XML file with Single NameSpace.

Now i have used XML SOurce which is using this new XML file and also generated XSD file.

But now the problem is that i am getting a long list in OUTPUT NAME(all the coulmn names individually and some other names like counter, sequence etc.) field when i click on Columns TAB of XML Source.

Please let me know how can i combine all these columns to populate a single table and where i am wrong.

Answer 1

you can change your XSLT in XML Task to combine elements or attributes together.

this is an example of XSLT to change structure of xml data ( you can flatten elements, or combine them and work with attributes as well):

http://www.rad.pasfu.com/index.php?/archives/21-XML-Task-Changing-Style-of-Data-XSLT.html

---

http://www.rad.pasfu.com

Answer 2

Thanks for the reply Reza.

Actually i have never before worked on XML and don't have any clue how to write XML code.

Can you please tell me if there is any general XSLT code which can convert any XML with Multiple Namespaces to Single Namespace??

Answer 3

There is no general XSLT code, you should write your own code.

I found this to remove namespaces:

http://wiki.tei-c.org/index.php/Remove-Namespaces.xsl

But didn't try it. I prefer always to write my own XSLT, this is simple language but very powerful. you can learn it very fast and write queries as you like:

http://www.w3schools.com/xsl/

if you couldn't solve the problem finally, put your xml content here and I will help you more in details

---

http://www.rad.pasfu.com

Answer 4

Thanks Reza.. I'll try to learn XSLT.. in case i'll face any issue i'll get back to you.

can i have your email id please?

Answer 5

This is how my XML file looks like:

<?xml version="1.0" encoding="utf-16"?>

<RGDRWStatus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <StatusID xmlns="http://tempuri.org/">0</StatusID>

  <StatusMessage xmlns="http://tempuri.org/">Successfully fetched the rows.</StatusMessage>

  <dsRGRWData xmlns="http://tempuri.org/">

    <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

      <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

        <xs:complexType>

          <xs:choice minOccurs="0" maxOccurs="unbounded">

            <xs:element name="Table">

              <xs:complexType>

                <xs:sequence>

                  <xs:element name=" ID" type="xs:decimal" minOccurs="0" />

                  <xs:element name="Name" type="xs:string" minOccurs="0" />

                  <xs:element name="Age" type="xs:decimal" minOccurs="0" />

                  <xs:element name="Address" type="xs:string" minOccurs="0" />

                  <xs:element name="State" type="xs:string" minOccurs="0" />

                  <xs:element name="City" 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">

      <NewDataSet xmlns="">

        <Table diffgr:id="Table1" msdata:rowOrder="0">

          < ID>1100</ ID>

          <Name>Manish</ Name >

          < Age >10</ Age >

          < Address >East Street, 24</ Address >

          < State >DEFG</ State >

          < City>Abcd</City>

</Table>

<Table diffgr:id="Table2" msdata:rowOrder="1">

          < ID>1100</ ID>

          <Name>Manish</ Name >

          < Age >10</ Age >

          < Address >East Street, 24</ Address >

          < State >DEFG</ State >

          < City>Abcd</City>

        </Table>

        <Table diffgr:id="Table3" msdata:rowOrder="2">

< ID>1100</ ID>

          < ID>1100</ ID>

          <Name>Manish</ Name >

          < Age >10</ Age >

          < Address >East Street, 24</ Address >

          < State >DEFG</ State >

          < City>Abcd</City>

        </Table>

        <Table diffgr:id="Table4" msdata:rowOrder="3">

< ID>1100</ ID>

          <Name>Manish</ Name >

          < Age >10</ Age >

          < Address >East Street, 24</ Address >

          < State >DEFG</ State >

          < City>Abcd</City>

        </Table>

      </NewDataSet>

    </diffgr:diffgram>

  </dsRGRWData>

</RGDRWStatus>

 

I tried to develop an XSLT filebut all i was getting is a blank XML file. Can you please help me in developing an XSLT file for this XML, so that the new XML has single Namespace. :(

Answer 6

What does your target table look like?

Answer 7

My Target table has following columns:

ID

NAME

Age

Address

State

City

Answer 8

You could insert this into a table using the xml datatype and methods, eg

DECLARE @xml XML

SELECT @xml = x.y
FROM OPENROWSET( BULK 'C:\Temp\temp.xml', SINGLE_BLOB ) x(y)


--ID NAME Age Address State City

--INSERT INTO ...
SELECT 
	d.c.value('(ID/text())[1]', 'INT'),
	d.c.value('(Name/text())[1]', 'VARCHAR(100)'),
	d.c.value('(Age/text())[1]', 'INT'),
	d.c.value('(Address/text())[1]', 'VARCHAR(100)'),
	d.c.value('(State/text())[1]', 'VARCHAR(100)'),
	d.c.value('(City/text())[1]', 'VARCHAR(100)')
FROM @xml.nodes('//NewDataSet/Table') d(c)

Answer 9

Alternately from SSIS, you could use something like this simplified XSD.  Remember when you autogenerate the xsd it is for the whole XML document.  If you only want to import part of the xml document then you only need part of the xsd.  This example worked for me using an XML Task in SSIS:

<?xml version="1.0" ?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="NewDataSet">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="Table">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:unsignedShort" />
              <xs:element name="Name" type="xs:string" />
              <xs:element name="Age" type="xs:unsignedByte" />
              <xs:element name="Address" type="xs:string" />
              <xs:element name="State" type="xs:string" />
              <xs:element name="City" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Answer 10

Alternately from SSIS, you could use something like this simplified XSD.  Remember when you autogenerate the xsd it is for the whole XML document.  If you only want to import part of the xml document then you only need part of the xsd.  This example worked for me using an XML Task in SSIS:

<?xml version="1.0" ?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="NewDataSet">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="Table">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:unsignedShort" />
              <xs:element name="Name" type="xs:string" />
              <xs:element name="Age" type="xs:unsignedByte" />
              <xs:element name="Address" type="xs:string" />
              <xs:element name="State" type="xs:string" />
              <xs:element name="City" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

WBob,

With this XSD file i am getting following error in SSIS:

[XML Source [2489]] Error: The component "XML Source" (2489) was unable to read the XML data. Root element is missing.

Answer 11

Sounds like your XML does not have a single root element or is invalid.  Can you open it in an XML editor - does this show the XML as invalid?

My SSIS package worked with a cleaned up version of the XML you posted above.  Is this the actual XML you are using or something different?

Answer 12

The XML file i posted above is the one i am using...This is what i am getting from the Web Service..

Answer 13

Create a simple SSIS package containing only an XML source.  Use it to point at some sample XML, and use the XSD I gave you.  Get this simple solution working; try and work out what is different between this simple working solution and yours.  I would guess the XML coming from the webservice is actually a bit different.  I used yours but had to clean it up a little - for example some of your elements have spaces in: this is not allowed.  One of your table sections has two ID elements - is this a mistake?

<?xml version="1.0" encoding="utf-16"?>
<RGDRWStatus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <StatusID xmlns="http://tempuri.org/">0</StatusID>
  <StatusMessage xmlns="http://tempuri.org/">Successfully fetched the rows.</StatusMessage>
  <dsRGRWData xmlns="http://tempuri.org/">
    <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
      <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
        <xs:complexType>
          <xs:choice minOccurs="0" maxOccurs="unbounded">
            <xs:element name="Table">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name=" ID" type="xs:decimal" minOccurs="0" />
                  <xs:element name="Name" type="xs:string" minOccurs="0" />
                  <xs:element name="Age" type="xs:decimal" minOccurs="0" />
                  <xs:element name="Address" type="xs:string" minOccurs="0" />
                  <xs:element name="State" type="xs:string" minOccurs="0" />
                  <xs:element name="City" 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">
      <NewDataSet xmlns="">
        <Table diffgr:id="Table1" msdata:rowOrder="0">
          <ID>1100</ID>
          <Name>Manish</Name>
          <Age>10</Age>
          <Address>East Street, 24</Address>
          <State>DEFG</State>
          <City>Abcd</City>
        </Table>
        <Table diffgr:id="Table2" msdata:rowOrder="1">
          <ID>1100</ID>
          <Name>Manish</Name>
          <Age>10</Age>
          <Address>East Street, 24</Address>
          <State>DEFG</State>
          <City>Abcd</City>
        </Table>
        <Table diffgr:id="Table3" msdata:rowOrder="2">
          <ID>1100</ID>
          <Name>Manish</Name>
          <Age>10</Age>
          <Address>East Street, 24</Address>
          <State>DEFG</State>
          <City>Abcd</City>
        </Table>
        <Table diffgr:id="Table4" msdata:rowOrder="3">
          <ID>1100</ID>
          <Name>Manish</Name>
          <Age>10</Age>
          <Address>East Street, 24</Address>
          <State>DEFG</State>
          <City>Abcd</City>
        </Table>
      </NewDataSet>
    </diffgr:diffgram>
  </dsRGRWData>
</RGDRWStatus>

If you are still stuck, this is my cleaned up version of your XML.  Try it.

Answer 14

Which element has space in it?? and yes two ID elements was a mistake in copying the XML..