Ebay API Question posted in
The official documentation of all the APIs offered by Ebay can be found here.

Ebay API – Read content of a text file as SELECT result (SQL Server)

I need a way to read the contents of a HTML file and return it as a column within a SQL server query.

To set the scene I’m working on a query that gathers all the data needed to export products from an ERP system and import it into eBay, but I need to store the product descriptions in a HTML file.

At part of the query, I need to read the content of that HTML file for each product SKU

The below isn’t correct SQL but should give an idea of what I’m trying to do:

SELECT '<HTML FILE CONTENTS>' as Description

The filename would be the same as the product SKU in the table and all files would be in the same folder, so I’d need to contaminate that into the file path.

Is this possible?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I've managed to do this using the below if anyones interested :)

    CREATE TABLE #files(filename VARCHAR(MAX))
CREATE TABLE #content1(content VARCHAR(MAX))
CREATE TABLE #content2(content VARCHAR(MAX))
CREATE TABLE #descriptions (
    SKU VARCHAR(MAX),
    Description VARCHAR(MAX)
)


INSERT INTO #files 
EXEC xp_cmdshell 'dir /B "D:ebay"'
DELETE FROM #files WHERE filename is NULL

DECLARE @filename varchar(MAX)
DECLARE @command VARCHAR(255)


WHILE (SELECT count(*) FROM #files) > 0
BEGIN
    DECLARE @description varchar(MAX)

    SELECT TOP 1 @filename = filename FROM #files

    SET @command = 'type D:ebay'+@filename+'"'
    INSERT INTO #content1
    EXEC xp_cmdshell @command
    
    --Merges multiple rows into single row
    INSERT INTO #content2
    SELECT STUFF((
        SELECT CHAR(13) + content
        FROM #content1
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')


    SELECT TOP 1 @description = content FROM #content2
    
    INSERT INTO #descriptions (SKU, Description) VALUES (REPLACE(@filename, '.html', ''), @description) 

    DELETE FROM #files WHERE filename = @filename
    DELETE FROM #content1
    DELETE FROM #content2
END

  2. 0

    I don’t think that’s possible in TSQL. How do you process the data after you have retrieved it? If you are using a programme, I would load the data into a List of objects and than loop over the objects and read the description from the HTML files separately.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search