Parsing XML with t-SQL in two separate tables



Usign SQL 2012 and trying to parse an XML to 2 separate tables in my database. Normaly 1 table would be enough, but not in this instance. My XML is structured as follows (I can't change it's structure, I already recieve it like that)



<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<podjetje id="" storitev="" uporabnik="" ts="" opis_storitve="">
<izdelki>
<izdelek st="1">
<izdelekID>ID</izdelekID>
<ean>EAN CODE</ean>
<izdelekIme>PRODUCT NAME</izdelekIme>
<url>WEBSITE</url>
<kratkiopis>SHORT DESCRIPTION</kratkiopis>
<opis>DESCRIPTION</opis>
<dodatneLastnosti>ATTRIBUTES</dodatneLastnosti>
<slikaVelika>BIG PICTURE URL</slikaVelika>
<dodatneSlike>
<dodatnaSlika1>EXTRA IMAGE URL</dodatnaSlika1>
<dodatnaSlika2>EXTRA IMAGE URL2</dodatnaSlika2>
<dodatnaSlika3>EXTRA IMAGE URL3</dodatnaSlika3>
</dodatneSlike>
</izdelek>
</izdelki>
</podjetje>


To insert this XML into a table i use SQL bulk insert



SET @SQLString = 'INSERT INTO tmpImport(XmlCol)
SELECT * FROM OPENROWSET(BULK ''' + @ImportFileName + ''', SINGLE_BLOB, ERRORFILE = ''' + @BulkLoadFilePath + ''') AS x '
EXECUTE (@SQLString)


I can handle most of the data without any problems. I ran into some problems when i get to the node "dodatneSlike". The idea is, that each article has some pictures. The main picture is in the node "slikaVelika" and I can insert it into my table. There are extra pictures in the child nodes of node "dodatneSlike". This is causing me problems, because I have to insert these extra pictures into a separate table (inserting the picture from node "slikaVelika" would also help, but I think i can get around it if it's not possible). The table is nothing special, just the Article ID from node "izdelekID" and the pictures from "dodatneSlike".


The problem is, I never know how many nodes ("dodatnaSlika1", "dodatnaSlika2",...) there will be. There might be 1, 10, 0....


So my question is how do I get the values from "dodatnaSlika" nodes?


No comments:

Post a Comment