How to: Replicate to and from XML Files

DD can import and export data from XML files. The format used for the XML files is the same as generated by the T-SQL language.

Database connection string format: Database=<data folder>;|xml|none/attr

Put <data folder> path into the Db. Path & Name field in the Distribution Location Card, where data files will be exported to or imported from, and select Version with XML mode.

Two XML formats are supported, standard Element mode (none) and Attribute mode (attr).

Each file should be named by Table name with .XML extension.

Example 1

Connection string: Database=c:\DBFiles;|xml|none

Data file name: Customer.xml

<Data>
  <row>
    <No.>011</No.>
    <Name>Spotsmeyer</Name>
    <SearchName>SPOTSMEYER</SearchName>>
    <Name2 />
    <Address>612 South Sunset Drive</Address>
  </row>
</Data>

T-SQL command to get same result: SELECT * from Customers FOR XML PATH, ROOT('Data')

Example 2

Connection string: Database=c:\DBFiles;|xml|attr

Data file name: Customer.xml

<Data> <row No.=”011” Name=”Spotsmeyer” SearchName=”SPOTSMEYER” Name2=”” Address=”6 Str” /></Data>

T-SQL command to get the same result: SELECT * from Customers FOR XML RAW, ROOT('Data')

When working with XML files, the Element and Attributes names cannot have special characters in the name, so DD supports name conversion that will also be used when exporting the data.

So a field named Search Name will become "Search_x0020_Name” in the XML file, and should be set as same for DD to be able to import the data from the file.

Tip: When you are importing from a Text file to LS Central, you can see how the Text file should be formatted and if everything works as it should by starting the export of data from Central to Text. You can then swap locations in the Scheduler Job to do the import.