|
Hi Mr
i have a problem in ssis when i tried to use xml file to import to any data sources
i used Multicast to test no or rows returned but no returned rows
i can't attach xml file in the message
please save as GetGroups.xml and test it and send me the result
="1.0"="utf-16"
<DataSet>
<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="T">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" minOccurs="0" />
<xs:element name="NodeGroup" type="xs:string" minOccurs="0" />
<xs:element name="NodeCount" type="xs:int" 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>
<T diffgr:id="T1" msdata:rowOrder="0">
<ID>648</ID>
<NodeGroup>Moustord</NodeGroup>
<NodeCount>5</NodeCount>
</T>
<T diffgr:id="T2" msdata:rowOrder="1">
<ID>646</ID>
<NodeGroup>Shell</NodeGroup>
<NodeCount>9</NodeCount>
</T>
<T diffgr:id="T3" msdata:rowOrder="2">
<ID>647</ID>
<NodeGroup>Suze</NodeGroup>
<NodeCount>4</NodeCount>
</T>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
modified 9-Oct-12 1:55am.
|
|
|
|
|
i tried to use OPENROWSET like that but it's not work can you modify it
DECLARE @xml XML
SET @xml = '<DataSet>
<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="T">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" minOccurs="0" />
<xs:element name="NodeGroup" type="xs:string" minOccurs="0" />
<xs:element name="NodeCount" type="xs:int" 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>
<T diffgr:id="T1" msdata:rowOrder="0">
<ID>648</ID>
<NodeGroup>Moustord</NodeGroup>
<NodeCount>5</NodeCount>
</T>
<T diffgr:id="T2" msdata:rowOrder="1">
<ID>646</ID>
<NodeGroup>Shell</NodeGroup>
<NodeCount>9</NodeCount>
</T>
<T diffgr:id="T3" msdata:rowOrder="2">
<ID>647</ID>
<NodeGroup>Suze</NodeGroup>
<NodeCount>4</NodeCount>
</T>
</NewDataSet>
</diffgr:diffgram>
</DataSet>'
;WITH XMLNAMESPACES(
'http://www.w3.org/2001/XMLSchema' AS ns,
'urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr )
SELECT
fd.c.value('(ID/text())[1]', 'INT') 'a',
fd.c.value('(NodeGroup/text())[1]', 'nvarchar(255)') 'b',
fd.c.value('(NodeCount/text())[1]', 'INT') 'c'
FROM @xml.nodes('ns:DataSet/diffgr:diffgram/NewDataSet/T') fd(c)
|
|
|
|