importing xml file to sql server

Hello there
I have an xml file that i need to compare it to data on server. and i don't
have xlt file (which is the base of xml as far as i know)
I need to import the xml file with the entire structure to sql server in
order to compare it with data i have
how can i do that?Roy Goldhammer wrote:

> I need to import the xml file with the entire structure to sql server in
> order to compare it with data i have
> how can i do that?
Have a look at the OPENXML clause
<http://msdn2.microsoft.com/en-us/library/ms191268.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/sql

importing xml file to sql server

Hello there
I have an xml file that i need to compare it to data on server. and i don't
have xlt file (which is the base of xml as far as i know)
I need to import the xml file with the entire structure to sql server in
order to compare it with data i have
how can i do that?
Roy Goldhammer wrote:

> I need to import the xml file with the entire structure to sql server in
> order to compare it with data i have
> how can i do that?
Have a look at the OPENXML clause
<http://msdn2.microsoft.com/en-us/library/ms191268.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Importing from XML - Base64 to image

I need to exchange image data from a SQL Server database with a client using
XML. The client will also be sending me image data (converted to base 64) in
an XML document.
The export has been made very easy using the FOR XML, BINARY BASE64 clause.
However I'm having trouble with the import as I cannot seem to find a
comparable method to import the data.
The data that will be imported will contain other elements in the XML
document and can be very large files.
I was having great success using the SQLXMLBulkLoad object in SQLXML to move
large XML files directly into the database. Unfortunately, I can't figure ou
t
how to use this tp import the base64 data and convert it into an image file
to put in the database.
Any clues how this can be done?
Thanks in advance for your help.Any help? Thanks.
"shawn" wrote:

> I need to exchange image data from a SQL Server database with a client usi
ng
> XML. The client will also be sending me image data (converted to base 64)
in
> an XML document.
> The export has been made very easy using the FOR XML, BINARY BASE64 claus
e.
> However I'm having trouble with the import as I cannot seem to find a
> comparable method to import the data.
> The data that will be imported will contain other elements in the XML
> document and can be very large files.
> I was having great success using the SQLXMLBulkLoad object in SQLXML to mo
ve
> large XML files directly into the database. Unfortunately, I can't figure
out
> how to use this tp import the base64 data and convert it into an image fil
e
> to put in the database.
> Any clues how this can be done?
> Thanks in advance for your help.|||To anyone who may be interested. I finally figured this out. D'oh! It's much
easier than it first appeared to me.
You can do it through an xsd mapping file the syntax will look something
like this for the base64 encoded element:
<xsd:element name="TifFileNode" type="xsd:base64Binary"
sql:field="TiffFileColumn" sql:datatype="image" />
"shawn" wrote:

> I need to exchange image data from a SQL Server database with a client usi
ng
> XML. The client will also be sending me image data (converted to base 64)
in
> an XML document.
> The export has been made very easy using the FOR XML, BINARY BASE64 claus
e.
> However I'm having trouble with the import as I cannot seem to find a
> comparable method to import the data.
> The data that will be imported will contain other elements in the XML
> document and can be very large files.
> I was having great success using the SQLXMLBulkLoad object in SQLXML to mo
ve
> large XML files directly into the database. Unfortunately, I can't figure
out
> how to use this tp import the base64 data and convert it into an image fil
e
> to put in the database.
> Any clues how this can be done?
> Thanks in advance for your help.

Importing CSV data into SQL database using DTS

I would like to import CSV data into a SQL data base using DTS. This
SQL database already contains the information however the CSV file
contains updated information.
data.csv
Computer Name | Serial Number | Processors | Processor Description |
Total Memory
computer 168 123456789 4 Intel 3.06 Ghz
3048 MB
SQL Table
ComputerName | SerialNumber | ProcNum | ProcessorType | Memory
Computer 168 123456789 2 Intel 2.8 ghz
2048 MB
As you can see the memory and processor and other data were updated in
the CSV which I need my import to:
- Find the computer name in the SQL Database table (even know the field
names are different), and then update the SerialNumber, ProcNum,
ProcessorType, Memory information.
- I also want to be sure if the asset name can't be found then it
should create a new row with the new information. Example if computer
218 is not in the SQL database it will populate the database with the
information.
Hi Wildatom
"Wildatom" wrote:

> I would like to import CSV data into a SQL data base using DTS. This
> SQL database already contains the information however the CSV file
> contains updated information.
> data.csv
> Computer Name | Serial Number | Processors | Processor Description |
> Total Memory
> computer 168 123456789 4 Intel 3.06 Ghz
> 3048 MB
> SQL Table
> ComputerName | SerialNumber | ProcNum | ProcessorType | Memory
> Computer 168 123456789 2 Intel 2.8 ghz
> 2048 MB
>
> As you can see the memory and processor and other data were updated in
> the CSV which I need my import to:
> - Find the computer name in the SQL Database table (even know the field
> names are different), and then update the SerialNumber, ProcNum,
> ProcessorType, Memory information.
You need something called a staging table to load this data into. You can
then update the existing records with the values from the staging table when
they match on a (primary) key value ( ComputerName )
UPDATE l
SET SerialNumber = s.SerialNumber
ProcNum = s.ProcNum
ProcessorType = s.ProcessorType
Memory = s.Memory
FROM livetable l
JOIN stagingtable s ON s.ComputerName = l.ComputerName
WHERE l.SerialNumber <> s.SerialNumber
OR l.ProcNum <> s.ProcNum
OR l.ProcessorType <> s.ProcessorType
OR l.Memory <> s.Memory

> - I also want to be sure if the asset name can't be found then it
> should create a new row with the new information. Example if computer
> 218 is not in the SQL database it will populate the database with the
> information.
>
Once you have updated the values you can insert anything that is not already
in the live table
INSERT livetable ( ComputerName, SerialNumber, ProcNum, ProcessorType,
Memory )l
SELECT s.ComputerName, s.SerialNumber, s.ProcNum, s.ProcessorType, s.Memory
FROM stagingtable s
LEFT JOIN livetable l ON s.ComputerName = l.ComputerName
WHERE l.ComputerName IS NULL
Alternatively you could use NOT EXISTS!
John

Importing CSV data into SQL database using DTS

I would like to import CSV data into a SQL data base using DTS. This
SQL database already contains the information however the CSV file
contains updated information.
data.csv
Computer Name | Serial Number | Processors | Processor Description |
Total Memory
computer 168 123456789 4 Intel 3.06 Ghz
3048 MB
SQL Table
ComputerName | SerialNumber | ProcNum | ProcessorType | Memory
Computer 168 123456789 2 Intel 2.8 ghz
2048 MB
As you can see the memory and processor and other data were updated in
the CSV which I need my import to:
- Find the computer name in the SQL Database table (even know the field
names are different), and then update the SerialNumber, ProcNum,
ProcessorType, Memory information.
- I also want to be sure if the asset name can't be found then it
should create a new row with the new information. Example if computer
218 is not in the SQL database it will populate the database with the
information.Hi Wildatom
"Wildatom" wrote:

> I would like to import CSV data into a SQL data base using DTS. This
> SQL database already contains the information however the CSV file
> contains updated information.
> data.csv
> Computer Name | Serial Number | Processors | Processor Description |
> Total Memory
> computer 168 123456789 4 Intel 3.06 Ghz
> 3048 MB
> SQL Table
> ComputerName | SerialNumber | ProcNum | ProcessorType | Memory
> Computer 168 123456789 2 Intel 2.8 ghz
> 2048 MB
>
> As you can see the memory and processor and other data were updated in
> the CSV which I need my import to:
> - Find the computer name in the SQL Database table (even know the field
> names are different), and then update the SerialNumber, ProcNum,
> ProcessorType, Memory information.
You need something called a staging table to load this data into. You can
then update the existing records with the values from the staging table when
they match on a (primary) key value ( ComputerName )
UPDATE l
SET SerialNumber = s.SerialNumber
ProcNum = s.ProcNum
ProcessorType = s.ProcessorType
Memory = s.Memory
FROM livetable l
JOIN stagingtable s ON s.ComputerName = l.ComputerName
WHERE l.SerialNumber <> s.SerialNumber
OR l.ProcNum <> s.ProcNum
OR l.ProcessorType <> s.ProcessorType
OR l.Memory <> s.Memory

> - I also want to be sure if the asset name can't be found then it
> should create a new row with the new information. Example if computer
> 218 is not in the SQL database it will populate the database with the
> information.
>
Once you have updated the values you can insert anything that is not already
in the live table
INSERT livetable ( ComputerName, SerialNumber, ProcNum, ProcessorType,
Memory )l
SELECT s.ComputerName, s.SerialNumber, s.ProcNum, s.ProcessorType, s.Memory
FROM stagingtable s
LEFT JOIN livetable l ON s.ComputerName = l.ComputerName
WHERE l.ComputerName IS NULL
Alternatively you could use NOT EXISTS!
John