XML Mapping Problem - 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 2005 Forums

SSIS and Import/Export (2005)

XML Mapping Problem

Author

Topic

dnlandes
Starting Member

3 Posts

Posted - 2008-10-09 : 13:07:02

I used this: http://support.microsoft.com/kb/316005 as a template for importing my xml data into a sql table however I have run into a problem.

Here is my XML data (a snipit of it anyway)

<RETS ReplyCode="0" ReplyText="V2.3.1 548: Success">
<COUNT Records="2965"/>
<REData>
<TRENDOffices>
<Office>
<TRENDOffice>
<OfficeTradingAs>Mason Dixon Realty-North East</OfficeTradingAs>
</TRENDOffice>
<ContactInformation>
<OfficeOfficePhone>4102872334</OfficeOfficePhone>
</ContactInformation>
<Address>
<OfficeCityName>North East</OfficeCityName>
<OfficeState>MD</OfficeState>
</Address>
<SystemInfo>
<OfficeID>ERAMAS</OfficeID>
</SystemInfo>
</Office>
<Office>
<TRENDOffice>
<OfficeTradingAs>Griffith Group Realty</OfficeTradingAs>
</TRENDOffice>
<ContactInformation>
<OfficeOfficePhone>7172745655</OfficeOfficePhone>
</ContactInformation>
<Address>
<OfficeCityName>Lebanon</OfficeCityName>
<OfficeState>PA</OfficeState>
</Address>
<SystemInfo>
<OfficeID>GRIFFITH</OfficeID>
</SystemInfo>
</Office>
<Office>
<TRENDOffice>
<OfficeTradingAs>Raymond F Riggs, Inc</OfficeTradingAs>
</TRENDOffice>
<ContactInformation>
<OfficeOfficePhone>6103982544</OfficeOfficePhone>
</ContactInformation>
<Address>
<OfficeCityName>Allentown</OfficeCityName>
<OfficeState>PA</OfficeState>
</Address>
<SystemInfo>
<OfficeID>RIGGSRAY</OfficeID>
</SystemInfo>
</Office>
</TRENDOffices>
</REData>
</RETS>

And the Mapping code

<?xml version="1.0" ?>
<Schema xmlns="urnTongue Tiedchemas-microsoft-com:xml-data"
xmlnsBig Smilet="urnTongue Tiedchemas-microsoft-com:xmlBig Smileatatypes"
xmlnsTongue Tiedql="urnTongue Tiedchemas-microsoft-com:xml-sql">
<ElementType name="OfficeOfficePhone" dt:type="string" />
<ElementType name="OfficeTradingAs" dt:type="string" />
<ElementType name="OfficeCityName" dt:type="string" />
<ElementType name="OfficeState" dt:type="string" />
<ElementType name="OfficeID" dt:type="string" />
<ElementType name="RETS" sql:is-constant="1">
<element type="REData" />
</ElementType>
<ElementType name="REData" sql:is-constant="1">
<element type="TRENDOffices" />
</ElementType>
<ElementType name="TRENDOffices" sql:is-constant="1">
<element type="Office" />
</ElementType>
<ElementType name="Office" sql:relation="Office1">
<element type="TRENDOffice" />
<element type="ContactInformation" />
<element type="Address" />
<element type="SystemInfo" />
</ElementType>
<ElementType name="TRENDOffice">
<element type="OfficeTradingAs" sql:field="OfficeTradingAs" />
</ElementType>
<ElementType name="ContactInformation" sql:is-constant="1">
<element type="OfficeOfficePhone" sql:field="OfficeOfficePhone" />
</ElementType>
<ElementType name="Address" sql:is-constant="1">
<element type="OfficeCityName" sql:field="OfficeCityName" />
<element type="OfficeState" sql:field="OfficeState" />
</ElementType>
<ElementType name="SystemInfo" sql:is-constant="1">
<element type="OfficeID" sql:field="OfficeID" />
</ElementType>
</Schema>

This is the SQL code

USE Office
CREATE TABLE Office1 (

OfficeTradingAs NVARCHAR(50),
OfficeOfficePhone NVARCHAR(20),
OfficeCityName NVARCHAR(20),
OfficeState NVARCHAR(20),
OfficeID NVARCHAR(20))

With this data and this map I get all the info in a table. Yay! But It puts OfficeTradingAs on one row then moves to the next row and puts OfficeOfficePhone on the next row and OfficeCityName, and OfficeCityState, etc. How can I modify this or what do I need to do to get all the info on one line? Any help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 13:15:00

cant you just use Data Flow Task in SSIS with XML source and OLEDB destination to extract data onto the table?