How to export data from SQL Server 2000 to XML

From Mdidwiki
Jump to: navigation, search

Most institutions already have image cataloging data in a database. If you are using Microsoft SQL Server 2000 as your database or if you can easily transfer your data to SQL Server, this article explains how to export that data to an XML file usable for import into MDID2.

As an example, I will use an old MDID1 database containing art history records. The original MDID1 used a Microsoft Access database, which could easily be upsized to SQL Server. Using Query Analyzer, we can query the table containing the data called ResolvedCatalog:

How to export data from SQL Server 2000 to XML query1.gif

Any table that has this basic structure can be exported:

  • One record per row
  • One column contains the record identifer
  • The name of the respective image file is in a column or can be derived from a column

The first step is to create a SELECT statement creating output that can be used with the FOR XML clause. SQL Server's syntax for doing this is somewhat complicated, but since we need a fairly simple XML file to import in MDID2, this example should be easy to adapt for your database.

In Query Analyzer, enter the following statement (for brevity, we'll only export the first three fields of each record):


SELECT

1 AS Tag,

NULL AS Parent,

'ImageID' AS [data!1!identifier],

NULL AS [record!2!resource],

NULL AS [record!2!ImageID!element]

NULL AS [record!2!CreatorName!element],

NULL AS [record!2!CreationYear!element],


UNION ALL


SELECT

2 AS Tag,

1 AS Parent,

'ImageID' AS [data!1!identifier],

ImageID+'.jpg' AS [record!2!resource],

ImageID AS [record!2!ImageID!element]

CreatorName AS [record!2!CreatorName!element],

CreationYear AS [record!2!CreationYear!element],

FROM MDID..ResolvedCatalog


To adapt this example to your database, you will have to change the following elements:

  • ImageID is the name of the identifer field in your MDID2 collection
  • In this example, we don't store the name of the associated image file in each record, but it can be derived from the image identifier by appending ".jpg". Replace the term ImageID+'.jpg' with a term that will result in the image file name for your records.
  • List all fields you want to export (in this example, ImageID, CreatorName, and CreationYear) and their corresponding name in your MDID2 collection (in this example, ImageID, CreatorName, and CreationYear). Please note that the corresponding name for the identifier field must be consistent throughout.
  • The name of the source database and table, MDID..ResolvedCatalog.

The output of this query should look like this:

How to export data from SQL Server 2000 to XML query2.gif

If you included all fields from your table, the statement will be rather long. In order to continue the export process, we must create a view that allows us to reference this output with a simple name:


CREATE VIEW DataExport

SELECT

1 AS Tag,

NULL AS Parent,

[statement created above continues here]


Again, replace the name DataExport with a name meaningful for your case. After running the CREATE VIEW statement, the following SELECT statement should result in the same output as the original statement:


SELECT * FROM DataExport


To make sure the output is correctly formatted, run the following statement - if you receive any error messages, go back and make sure everything is set up correctly:


SELECT * FROM DataExport FOR XML EXPLICIT


At this point we are ready to export the data to an XML file. Open a command prompt and run SQL Server's bcp tool:


bcp "SELECT * FROM MDID..DataExport FOR XML EXPLICIT" queryout data.xml -w -r "" -S sqlserver -T


You will have to adapt the following parameters:

  • The name of the database and the view you created (MDID..DataExport)
  • The name of the XML output file (data.xml), pick a name meaningful for your case.
  • The name of your SQL Server (sqlserver)
  • The -T parameter causes bcp to use a trusted connection to your server, if you have to log in with a username and password, use the -U username -P password parameters instead.

The resulting data.xml file is now ready for import into MDID2.

Personal tools
Namespaces

Variants
Actions
Navigation
Toolbox