read a xml into SQL-Server

–temp table variable

DECLARE @tmpXML TABLE (XmlColumn xml)

–import xml-file into SQL-Server

INSERT INTO @tmpXML(XmlColumn)

SELECT * FROM OPENROWSET(

BULK ‘C:\Daten\media\list.xml’,

SINGLE_BLOB) AS x;

–query  of xml-node ‘filename’ reads all nodes ‘filename’

WITH XMLNAMESPACES (‘http://winscp.net/schema/session/1.0’ as w)

INSERT INTO @Datei(Dateiname)

SELECT T.c.value(‘@value’, ‘varchar(500)’)

FROM @tmpXML

CROSS APPLY XmlColumn.nodes(‘//w:filename’) T(c);

IF EXISTS(SELECT * FROM @Datei WHERE Dateiname = @EAN + ‘.jpg’)

SET @Bild1 = @EAN + ‘.jpg’ ELSE SET @Bild1 = ”

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s