Bericht 8 van 27NL Computer Forum ~ SQL & Programmeren Van | : | Ronald Beuker | Datum | : | 13-03-2008 |
Aan | : | Hugo Kornelis | MsgID | : | 3771.8 |
Onderwerp | : | XML-velden gesplitst importeren SQL2005 | Forum | : | ws-nlcomputer |
Hoi Hugo,
Hartelijk dank voor je antwoorden! Kwam je zomaar toevallig even langs?
>> Ik neem aan dat je weet dat je in SQL Server 2005 ook een XML datatype hebt, waarin je de order zo uit het XML bestand kunt inlezen? Met XQuery kan je dan de onderdelen er uit peuteren. <<
Nou, dit zegt me eigenlijk nog helemaal niets. <g> Ik had wel ergens opgepikt dat je met zo'n (inderdaad)
nvarchar(MAX) heel makkelijk een 'rommelig' bestand kunt importeren, waarna je er vervolgens weer mee verder aan de slag kunt gaan. Het importeren van het XML-bestand heb ik door SSIS laten inregelen (die maakte ook zelf een XSD-bestand).
Het teken heb ik kunnen vinden: een 0D0A teken. Maar na het importeren was het teken foetsie. Dus moest ik wat anders verzinnen...
Ik heb het opgelost met een freeware tool die XVI32 heet. Hiermee kun je met een batch bestandje in 1 keer een bepaald teken laten vervangen! Het lukte niet om 0D 0A te vervangen (in XVI32 zag ik ook alleen maar 0A staan; dit wijkt dus af van wat ik in UltraEdit zie), maar het lukte me wel om 0A te vervangen door 3B. Dat is een puntkomma.
Een neveneffect was wel dat in het XML-bestand nu ook alle normale regeleinden werden vervangen door puntkomma's, dus het XML-bestand leek nergens meer op. <g> Maar SQL Server 2005 piepte nergens over! En dus kreeg ik keurig in mijn NVARCHAR(MAX) kolom die
stathist heet:
2008-03-04 17:32 A1;2008-03-05 10:59 A2;2008-03-05 12:16 A3;2008-03-05 21:41 abcdefgh;2008-03-05 21:47 abcdefg;2008-03-05 21:56 abcdefghij;2008-03-06 23:42 A4;2008-03-07 11:43 A5;2008-03-10 09:11 A6En toen kwam dus het opsplitsen aan de orde. Ik heb gekeken op de site van Sommarskog, maar ik kwam er niet uit.
De voorbeeld tabel die hier bovenaan staat, sprak mij zeer aan:
http://www.sommarskog.se/arrays-in-sql-2005.html#tablelists Maar hier haakte ik af:
CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS
SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
JOIN iter_intlist_to_tbl(@ids) i ON P.ProductID = i.number
go
EXEC get_product_names_iter '9 12 27 37'Wat moet ik nou met die rare 9, 12, 27 en 37? En dan had-ie ook nog van die nóg uitgebreidere voorbeelden, compleet met Sloveense collations (vond ik dubieus om zomaar te gaan gebruiken). Maar goed, ik ga er nog wel eens goed op studeren... <g>
Elders op Internet vond ik wel een TsqlSplit functie die wél deed wat ik wilde:
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@List As varchar(8000))
RETURNS @Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @Item As varchar(8000), @Pos As int
WHILE DATALENGTH(@List)>0
BEGIN
SET @Pos=CHARINDEX(',',@List)
IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
IF @Item<>'' INSERT INTO @Items SELECT @Item
SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
END
RETURN
END
GOVervolgens kreeg ik met deze query wat ik wilde:
INSERT INTO STATUSTEMP (orderID, status)
SELECT orderID, item as status
FROM XMLTEMP
CROSS APPLY TsqlSplit(stathist) AS M
order by orderID, statusDaarna was het nog een kwestie van jouw tips erin verwerken:
INSERT INTO STATUSTEMP2 (orderID, statusDateTime, status)
SELECT orderID, LEFT(status,10) + 'T' + SUBSTRING(status,12,5) + ':00' AS statusDateTime, STUFF(status,1,17,'') As status
FROM STATUSTEMPTrouwens, STUFF?? Ik had er nog nóóit van gehoord. Maar het werkt perfect, dus ik maak er dankbaar gebruik van. <g>
Doe ik dit nou een beetje 'logisch', of doet mijn werkwijze de wenkbrauwen fronsen?
Ik wil morgen dan gaan proberen om de kolomdefinitie te veranderen van NVARCHAR in een echte datum/tijd kolom, ik ben benieuwd. <g>
Groeten,
Ronald (Sysop)
Bericht 9 van 27NL Computer Forum ~ SQL & Programmeren Van | : | Ronald Beuker | Datum | : | 13-03-2008 |
Aan | : | Tony de Jonge (Sysop) | MsgID | : | 3771.9 |
Onderwerp | : | XML-velden gesplitst importeren SQL2005 | Forum | : | ws-nlcomputer |
Hoi Tony,
>> Hmm, ik kom tegenwoordig redelijk wat XML tegen (de nieuwste generatie betaalautomaten wenst ook zo aangesproken te worden) <<
Is dat soms een anti-skimming maatregel? <g>
>>Verder zou ik m'n XML nooit zo structureren, maar da's wat anders en daar heb je nu niets aan want de bron is een gegeven feit. <<
Helemaal mee eens. <g> Het lijkt wel alsof iemand heeft zitten kopiëren/plakken, vanuit een gewone relationele tabel in een XML-bestand!
>>Als je een keer een voorbeeld hebt dat je 'gemanipuleerd' wilt zien weil ik best even proberen hoe het reageert etc., programmeren is iets wat ik wel eens vaker doe <g> <<
Leuk! Ik heb een klein voorbeeldje gemaakt, dat is afgeleid van een echt bestand (zie de inhoud van het zip-bestand bij dit bericht). Ik heb dit uiteraard anoniem gemaakt, en de inhoud heel simpel gehouden. Het betreffende 0D 0A teken zit er in ieder geval nog steeds in. Het XML-bestand is trouwens
niet 'well formed', dat weet ik (geen geldig root element), maar het gaat nu even om het vervangen van dat teken.
Met XVI32 (zie mijn bericht aan Hugo) lukt het mij alleen om het hele bestand op 1 regel te zetten, en overal puntkomma's tussen te zetten (ipv alleen waar ik puntkomma's wil hebben, dus binnen de <stathist></stathist> elementen). Ik ben benieuwd of dat beter kan. <g>
Groeten,
Ronald
| Bijlagen : voorbeeld.zip 1KB |
Bericht 10 van 27NL Computer Forum ~ SQL & Programmeren Van | : | Hugo Kornelis | Datum | : | 14-03-2008 | Aan | : | Ronald Beuker | MsgID | : | 3771.10 | Onderwerp | : | XML-velden gesplitst importeren SQL2005 | Forum | : | ws-nlcomputer |
Hoi Ronald, Kwam je zomaar toevallig even langs? Laten we zeggen dat ik zomaar opeens het gevoel had dat mijn expertise hier nodig was o:-) En toen kwam dus het opsplitsen aan de orde. Ik heb gekeken op de site van Sommarskog, maar ik kwam er niet uit Ik heb net even gekeken, en ik zie dat Erland sinds mijn laatste bezoek heel veel informatie heeft toegevoegd - zeer informatief, maar het maakt het wel onmogelijk om "even snel" een goede methode van zijn site te plukken. Daarnaast zie ik dat hij in de versies voor SQL Server 2005 overal rekening houdt met argumenten van meer dan 8000 bytes (8000 tekens vooor varchar, of 4000 voor nvarchar). Dat maakt de code complexer - vooral omdat hij (om snelheid te winnen) handmatig de invoer in stukken van 8000 bytes hakt... Maar hier haakte ik af: (...) Wat moet ik nou met die rare 9, 12, 27 en 37? Dat was bedoeld als voorbeeld van hoe je zo'n functie kunt aanroepen (met de -in dit geval- spatie-seperated values list als hardgecodeerd argument tbv het voorbeeld) En dan had-ie ook nog van die nóg uitgebreidere voorbeelden, compleet met Sloveense collations Aha! Betrapt - je hebt niet de hele site van voor tot achter gelezen! (Erland legt ergens uit dat hij een binaire collatie forceert om iets snelheid te winnen - een komma, puntkomma of spatie is immers toch hetzelfde in elke denkbare collatie - en dat hij kiest voor Sloveens omdat zijn testdata Sloveens is, maar elke binaire collatie gebruikt kan worden. Ik zeg, als je niet vast zit aan het uit het systeem persen van de laatste druppel performance, laat dit dan lekker zitten <g>). Elders op Internet vond ik wel een TsqlSplit functie die wél deed wat ik wilde Hihi. Dat is dus dezelfde functie die Erland op zijn site de naam REALSLOW gegeven heeft. Je mag drie keer raden waarom Persoonlijk vind ik de methode via een tabel met getallen erg handig. Heb je die nog niet, maak hem dan (eenmalig) - een tabel met één kolom, integer en primary key, gevuld met alle getallen van 1 tot en met een afdoende hoog getal - meestal is een miljoen wel voldoende. Zo'n tabel komt in heel veel gevallen goed van pas; naar mijn idee zou Microsoft hem gewoon mogen opnemen in de model database zodat elke nieuwe database er standaard over beschikt. CREATE TABLE dbo.Numbers (Number int NOT NULL PRIMARY KEY); WITH digits (d) AS ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) INSERT dbo.Numbers (Number) SELECT Number FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 + v.d * 10000 + vi.d * 100000 AS Number FROM digits i CROSS JOIN digits ii CROSS JOIN digits iii CROSS JOIN digits iv CROSS JOIN digits v CROSS JOIN digits vi) AS Numbers; Heb je die tabel, dan kan je de volgende vereenvoudigde vorm van inline_split_me (zie Erlands site voor de details) gebruiken. Ik ga uit van een varchar(8000) parameter, dus geen unicode en geen "onbeperkte" langte. Klopt dat uitgnagspunt niet, dan post ik graag een aangepaste versie.
CREATE FUNCTION dbo.inline_split_me(@param varchar(8000)) RETURNS TABLE AS RETURN (SELECT SUBSTRING(@param, Number, CHARINDEX(',', @param + ',', Number) - Number) AS Value FROM dbo.Numbers WHERE Number <= LEN(@param) AND SUBSTRING(',' + @param, Number, 1) = ','); go Die zal vermoedelijk een heel stuk sneller werken dan wat je nu hebt. Misschien niet het snelst mogelijke, maar vermoedelijk wel goed genoeg voor wat jij wil. Trouwens, STUFF?? Ik had er nog nóóit van gehoord. Dan moet je toch wat vaker mijn blog lezen. Anderhalf jaar geleden heb ik uitgebreid over deze (inderdaad veel te onbekende) functie geschreven. Zie http://sqlblog.com/blogs/hugo_kornelis/archive/2006/10/12/Stuff.aspx Doe ik dit nou een beetje 'logisch', of doet mijn werkwijze de wenkbrauwen fronsen? Op zich is het best wel prima, alleen zie ik wel een paar kleinigheden. De functie die je nu hebt heeft varchar(8000) als parameter (net als mijn suggestie, dus), maar in je tekst schrijf je over nvarchar. We zitten toch niet nodeloos heen en weer te converteren, Beuker? Verder is het gebruik van tijdelijke tabellen helemaal niet nodig. Ik weet niet wat je met STATUSTEMP2 doet, maar STATUSTEMP kan je in elk geval laten vervallen door de twee queries als volgt te combineren: INSERT INTO #STATUSTEMP2 (orderID, statusDateTime, status) SELECT x.orderID, CAST(LEFT(M.Value,10) + 'T' + SUBSTRING(M.Value,12,5) + ':00') AS datetime), STUFF(M.Value,1,17,'') FROM XMLTEMP AS x CROSS APPLY dbo.inline_split_me(stathist) AS M;
Zoals je ziet heb ik ook de ORDER BY laten vervallen (aangezien een tabel per definitie een ongeordende verzameling is, heeft het volgens mij geen zin om rijen te sorteren voordat je ze in de bak gooit), van de tijdelijke tabel een échte #TijdelijkeTabel gemaakt, en alvast de conversie naar datetime toegevoegd. Heb je morgen weer tijd voor andere zaken :-D Laat het me maar weten als er nog vragen zijn. Groetjes, Hugo Kornelis, SQL Server MVP-- Kijk ook eens op mijn blog: http://sqlblog.com/blogs/hugo_kornelis |
Bericht 11 van 27NL Computer Forum ~ SQL & Programmeren Van | : | Hugo Kornelis | Datum | : | 14-03-2008 |
Aan | : | Ronald Beuker | MsgID | : | 3771.11 |
Onderwerp | : | XML-velden gesplitst importeren SQL2005 | Forum | : | ws-nlcomputer |
Hoi Ronald,
Ben ik nog een keer. Ik zag je bericht aan Tony nog niet toen ik aan mijn vorige antwoord begon.
Ik heb een klein voorbeeldje gemaaktEn dat heb ik gebruikt om eens te kijken hoe ver ik kom met het XML datatype en de bijbehorende functies. Zie het volgende voorbeeld (na het met copy/paste vanuit jouw XML bestand in de editor plakken van de XML waren de regelovergangen veranderd in alleen CHAR(10), dus daarom test ik daarop - jij zal CHAR(13)+CHAR(10) moeten gebruiken.
DECLARE @a xml;
SET @a = '<order>
<orderID>123456789</orderID>
<stathist>2008-02-20 09:23 A1
2008-02-20 10:55 A2
2008-03-13 21:22 akdhkhds
2008-03-13 21:29 adsa</stathist>
</order>
<order>
<orderID>AA-1234567</orderID>
<stathist>2008-02-20 14:03 A1
2008-02-20 14:39 A1
2008-03-07 20:58 fdsfdsfdsf
2008-03-07 21:07 asdassad
2008-03-07 21:21 adsdd
2008-03-08 18:20 A2
2008-03-13 10:53 A4</stathist>
</order>';
SELECT Ord.value('orderID[1]','varchar(20)') AS OrderID,
Ord.value('stathist[1]','varchar(8000)') AS StatHist,
REPLACE(Ord.value('stathist[1]','varchar(8000)'), CHAR(10), ';') AS StatHistGeschoond
FROM @a.nodes('order') AS Orders(Ord);Dit kan je dan weer als onderdeel opnemen in de query uit mijn vorige bericht, zodat je dus het volgende krijgt:
INSERT INTO #STATUSTEMP2 (orderID, statusDateTime, status)
SELECT x.orderID, CAST(LEFT(M.Value,10) + 'T' + SUBSTRING(M.Value,12,5) + ':00' AS datetime), STUFF(M.Value,1,17,'')
FROM (SELECT Ord.value('orderID[1]','varchar(20)') AS orderID,
REPLACE(Ord.value('stathist[1]','varchar(8000)'), CHAR(10), ',') AS stathist
FROM @a.nodes('order') AS Orders(Ord)) AS x
CROSS APPLY dbo.inline_split_me(x.stathist) AS M;
(NB - ten opzichte van mijn vorige bericht heb ik één verkeerd geplaatste sluithaakgecorrigeerd en ik heb voor het scheidingsteken in de REPLACE een komma in plaats van een puntkomma gezet omdat ik vergeten was de komma's in de splits-functie door puntkomma's te vervangen).
Groetjes,
Hugo Kornelis, SQL Server MVP
--
Kijk ook eens op mijn blog:
http://sqlblog.com/blogs/hugo_kornelis
Bericht 12 van 27NL Computer Forum ~ SQL & Programmeren Van | : | Ronald Beuker | Datum | : | 14-03-2008 |
Aan | : | Ronald Beuker | MsgID | : | 3771.12 |
Onderwerp | : | XML-velden gesplitst importeren SQL2005 | Forum | : | ws-nlcomputer |
Hoi Tony,
Ik ben zelf alweer een stap verder gekomen. Ik gebruik nog steeds XVI32 en doe daarmee het volgende:
ADR 0
REPLACEALL 0A BY 3B
ADR 0
REPLACEALL 3E 3B BY 3E 0A
ADR 0
INSERT 3C 3F 78 6D 6C 20 76 65 72 73 69 6F 6E 3D 22 31 2E 30 22 20 65 6E 63 6F 64 69 6E 67 3D 22 69 73 6F 2D 38 38 35 39 2D 31 22 20 3F 3E 0A
In gewone(re) mensentaal:
1. Ga naar het begin van het bestand.
2. Vervang alle regeleinden door een puntkomma.
3. Ga weer terug naar het begin van het bestand.
4. Vervang overal >; door ><regeleinde>.
5. Ga weer terug naar het begin van het bestand.
6. Zet bovenaan nog even <?xml version="1.0" encoding="iso-8859-1" ?>
Bij stap 4 zorg ik er dus voor dat niet meer alles op 1 regel staat (want dat was het gevolg van stap 2). Stap 6 is een extraatje. Eigenlijk zou deze regel al in het XML-bestand moeten zitten, maar het schijnt nogal moeilijk te zijn om dat erin te krijgen. Nou ja, dan zet ik het er zelf maar boven. <g> Dankzij deze regel struikelt SSIS niet meer over Latin-1 tekens. Het pakket wordt nl. gebruikt door call center agents die zélf hun naam kunnen typen (ja, ik verzin het niet). En als Léon vindt dat hij geen Leon heet, dan gaat SSIS zonder die 1e regel op z'n gat. <g>
Kortom, ik ben er uit! Dat neemt niet weg dat ik benieuwd ben hoe jij dit zou aanpakken. ;-)
Groeten,
Ronald
Bericht 13 van 27NL Computer Forum ~ SQL & Programmeren Van | : | Ronald Beuker | Datum | : | 14-03-2008 |
Aan | : | Tony de Jonge (Sysop) | MsgID | : | 3771.13 |
Onderwerp | : | XML-velden gesplitst importeren SQL2005 | Forum | : | ws-nlcomputer |
Hoi Tony,
Zie bericht 3771.12. Mijn antwoord aan jou kwam opeens bij mezelf terecht. <g>
Groeten,
Ronald
Bericht 14 van 27NL Computer Forum ~ SQL & Programmeren Van | : | Ronald Beuker | Datum | : | 14-03-2008 |
Aan | : | Hugo Kornelis | MsgID | : | 3771.14 |
Onderwerp | : | XML-velden gesplitst importeren SQL2005 | Forum | : | ws-nlcomputer |
Hoi Hugo,
En ik las jouw reactie weer nádat ik zelf nogmaals naar Tony had gereageerd. <g>
Leuk om te zien dat het ook zó kan, het werkt inderdaad! Op die manier is SSIS alweer een heel stuk minder nodig. Ik denk wel dat ik het bij mijn huidige oplossing houd (XML importeren via SSIS); zie verder mijn andere bericht (als dat er nog niet staat: dan ben ik er nog mee bezig <g>).
Groeten,
Ronald