Sql-server – Would appreciate some advice on using external data with SQL Server 2005

sql serverxml

I'm a web developer with a bit of DB experience, but this one is beyond my knowledge.

We're trying to set up a new website. The data – products, prices, descriptions etc – are supplied by an external company in either .XLS, .CSV or XML format. This data is updates & distributed regularly, eg each month.

I could simply import all the data each time – the problem is we don't necessarily need all the products, so we would mark them as "unavailable" or something by creating a new field in the DB.

So, next time the data is provided, I need to import it again – but making sure it doesn't overwrite our "available" field.

My initial thought was to create 2 tables – one, "Products" which I would import the data into each time and a new table "availability" just with 2 fields – product ID and an available – yes/no field and join the tables on the product ID.

Is this a sensible approach? can XML files be read directly from SQL Server without importing?

Best Answer

You can import your data into a staging table then update the product reference data from that. The product table can hold your 'available' flag. Unfortunately you will have to import the XML data into SQL Server to load it, but SSIS has an XML reader, so you can use that to do the imports.

Related Solutions

Sql-server – Converting a VARCHAR to VARBINARY

You need to use a specific style when you expect to keep the same binary value when converting from a string. Otherwise SQL Server tries to encode the string the same way it would encode 'bob' or 'frank'.

That said, your input string doesn't look correct - there is either a byte missing or one byte too many. This works fine if I drop the trailing E:

SELECT CONVERT(VARBINARY(25), '0x9473FBCCBC01AF', 1);
------------ the ,1 is important ---------------^^^

Result is binary:

----------------
0x9473FBCCBC01AF

MySQL – include a default value in LOAD XML INFILE statement

When one needs to do something more complex that is available in the syntax, it is often useful to 'stage' the data through a temp table:

Perhaps:

CREATE TEMPORARY TABLE tmp ( ... DEFAULT 'myfile' ... );
LOAD XML ...;
INSERT INTO tab1 SELECT * FROM tmp;

or:

CREATE TEMPORARY TABLE tmp LIKE tab1;
LOAD XML ...;
UPDATE tmp SET DEFAULT = 'myfile';
INSERT INTO tab1 SELECT * FROM tmp;

Best Answer

Related Solutions

Sql-server – Converting a VARCHAR to VARBINARY

MySQL – include a default value in LOAD XML INFILE statement

Related Question