Hallo

Welkom, Gast. Alsjeblieft inloggen of registreren.

Recent

213 gasten, 0 leden

Welkom, Gast. Alsjeblieft inloggen of registreren.

28 maart 2024, 10:03:18

Login met gebruikersnaam, wachtwoord en sessielengte

Nieuws

Welkom op het vernieuwde NL Computer Forum!

Auteur Topic: XML-velden gesplitst importeren SQL2005  (gelezen 23064 keer)

0 leden en 1 gast bekijken dit topic.

Offline NLCOMP

  • Forumheld
  • *****
  • Berichten: 14.666
    • NL Computer Forum
XML-velden gesplitst importeren SQL2005
« Gepost op: 9 november 2009, 20:26:59 »
Bericht 1 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:10-03-2008
Aan:AllenMsgID:3771.1
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Beste Forumleden,
Ik heb weer eens een leuke uitdaging.   Een klant van ons heeft een -laten we zeggen- minder gangbaar orderinvoer- en afhandelingssysteem. De data zijn alleen via een webbrowser te zien en te muteren, en als export is er alleen een XML-feed beschikbaar. Rechtstreeks toegang tot de brontabellen in de database is helaas niet mogelijk. De gegevens moeten echter wel geanalyseerd kunnen worden, en derhalve wil ik ze in eerste aanleg in een normale relationele database stoppen.
In de XML-feed wordt per order van alles bijgehouden, waaronder de historie. Dat gaat op een -vind ik- nogal typische manier, nl.:
<order>
<orderID>12345678</orderID>
<stathist>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 abcdefgh 2008-03-06 23:42 A4 2008-03-07 11:43 A5 2008-03-10 09:11 A6</stathist>
</order>

Dit zie je als je het XML-bestand in bijv. de Internet Explorer opent (lijkt nergens op). In een echte editor als UltraEdit ziet het er al wat beter uit:
<order>
<orderID>12345678</orderID>
<stathist>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 A6</stathist>
</order>

Voorafgaand aan een nieuwe datum/tijd staat dus een soort van regelomhaal, ik denk een Shift-Enter.
De gehele 'stathist' regel importeren in SQL Server 2005 gaat me nog wel lukken (gewoon in z'n geheel in een varchar-kolom gooien). Maar dan... hoe kan ik dat het beste splitsen? De statuscodes zijn óf 2 tekens breed, óf tot iets van 10 tekens. Iets doen met een vaste breedte gaat dus al niet.
Het enige dat dan -denk ik- over blijft, is die zachte regelomhaal. Dat is in ieder geval een soort van scheidingsteken. Daarbij heb ik dan wel 3 zorgen/vragen:
1. Hoe kom ik erachter welk teken dat nu precies is?
2. Komt dat (nu nog onbekende) teken wel goed mee als ik de hele stathist-regel eerst in z'n geheel importeer?
3. Hoe gebruik ik dat scheidingsteken dan vervolgens in SSIS?
Het zou ook kunnen dat je zo'n probleem ook nog heel anders kunt oplossen. Voor wie aan het manipuleren van het bron XML-bestand zit te denken: het (tekst)bestand is zo'n 3 MB groot.
Ik ben zeer benieuwd naar alle ideeën! Het uiteindelijke doel is een hele simpele tabel, met 3 kolommen:
orderID  DatumTijd         Status
-------------------------------------

12345678 2008-03-04 17:32  A1
12345678 2008-03-05 10:59  A2
12345678 2008-03-05 12:16  A3
12345678 2008-03-05 21:41  abcdefgh
12345678 2008-03-05 21:47  abcdefg
12345678 2008-03-05 21:56  abcdefgh
12345678 2008-03-06 23:42  A4
12345678 2008-03-07 11:43  A5
12345678 2008-03-10 09:11  A6

Groeten,
Ronald (Sysop)


Bericht 2 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Michel Uphoff (Sysop)Datum:10-03-2008
Aan:Ronald BeukerMsgID:3771.2
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Hoi Ronald,
Al in de hex mode van Uedit gekeken naar de hex codes van die line break (meestal is dat 0D0A, oftewel decimaal 13, 10, oftewel CR/LF).
Je zou bijvoorbeeld die 0D0A kunnen vervangen door een teken wat wel eenvoudig wordt herkend. Geen idee of dit eenmalig is, en zo ja dan doet Uedit dit in een vloek en een zucht voor je. Je zou bijvoorbeeld de pipe | kunnen gebruiken.

Michel Uphoff (NLcomputer)
Homepage



Bericht 3 van 27

NL Computer Forum ~ SQL & Programmeren
Van:PeterDatum:10-03-2008
Aan:Ronald BeukerMsgID:3771.3
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer
Hoi Ronald,

>>Ik heb weer eens een leuke uitdaging.<<
Ik heb geen ervaring met XML bestanden maar het hele bestand inlezen en als tekst bewerken moet te doen zijn. De grootte, 3MB, lijkt me geen probleem.
Open het bestand eens met UltraEdit en schakel HEX-edit in (Ctrl+H), of je dan een scheidingsteken ziet.
Zonee, dan kun je elke 3e spatie tussen <stathist> en </stathist> als scheiding gebruiken.
 
Hoe je de uitvoer wilt is een ander verhaal.


groeten, Peter



Bericht 4 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Hugo KornelisDatum:11-03-2008
Aan:Ronald BeukerMsgID:3771.4
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Hoi Ronald,
SQL uitdagingen zijn mijn boter en brood, dus kom maar op <g>.
>>De gehele 'stathist' regel importeren in SQL Server 2005 gaat me nog wel lukken (gewoon in z'n geheel in een varchar-kolom gooien).<<
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. Maar aangezien je dat niet vraagt neem ik aan dat je daar al achter was en dat je nu een varchar(MAX) of varchar(8000) kolom hebt waar alleen de statinfo in staat:
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 A6

(Overigens, afhankelijk van wat er exact in de data kan staan moet je misschien Nvarchar in plaats van varchar gebruiken - XML data is per definitie unicode, en bij de conversie naar 8-bit ASCII kan informatie verloren gaan).
Dit is uiteraard zo onrelationeel als maar kan, dus je hebt groot gelijk dat je dat wil omvormen naar een nette relationele tabel met drie kolommen en een heleboel rijen.
>>1. Hoe kom ik erachter welk teken dat nu precies is?<<DECLARE @Statinfo varchar(8000)
-- Testgegevens, vervang door echte data
SET
@Statinfo ='2008-03-04 17:32 A1
2008-03-05 10:59 A2 (etc)'
;

SELECTASCII(SUBSTRING(@Statinfo, 20, 1)), ASCII(SUBSTRING(@Statinfo, 21, 1)); Let er op dat er soms meer dan één teken voor de regelovergang gebruikt wordt. Bovenstaande code toont dat de tekst die ik vanuit het bericht op het Forum geplakt heb een CHAR(13) en een CHAR(10) (de oude vertrouwde CR/LF) gebruikt voor de regelovergang.
>>2. Komt dat (nu nog onbekende) teken wel goed mee als ik de hele stathist-regel eerst in z'n geheel importeer?<<
Zul je moeten proberen. Laat het me weten als het niet lukt
>>3. Hoe gebruik ik dat scheidingsteken dan vervolgens in SSIS?<<
Van SSIS heb ik totaal geen kaas gegeten, maar er zijn wel technieken om een tekstveld op basis van een bekend scheidingsteken te splitsen. Ik hoef die hier niet te beschrijven, want aan de compleetheid van Erland Sommarskog kan ik sowieso niet tippen. Zie dus http://www.sommarskog.se/arrays-in-sql.html.
Met een van de daar beschreven technieken kan je de tekst splitsen in een tabel met één kolom (die dan nog de combinatie van datum en status bevat) en een groot aantal rijen. Aangezien datum/tijd zo te zien een vaste lengte heeft, kan je de datum/tijd van de status splitsen met LEFT(xxx, 16) voor datum/tijd en met STUFF(xxx, 1, 17, '') voor de status.
Let er bij de datumconversie op dat "yyyy-mm-dd hh:mm" geen datum/tijd formaat is dat SQL Server gegarandeerd goed begrijpt. Je kan misverstanden voorkomen door CONVERT te gebruiken met een style parameter (zoek even de juiste op in Books Online), of door wat string manipulatie te gebruiken om de datum om te werken naar "yyyy-mm-ddThh:mm:ss" (dus een T ertussen en :00 er achter). Laat het me maar even weten als je hier hulp bij nodig hebt.
Groetjes,
Hugo Kornelis, SQL Server MVP
--
Kijk ook eens op mijn blog: http://sqlblog.com/blogs/hugo_kornelis


Bericht 5 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Tony de Jonge (Sysop)Datum:11-03-2008
Aan:Ronald BeukerMsgID:3771.5
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Ronald,
Hmm, ik kom tegenwoordig redelijk wat XML tegen (de nieuwste generatie betaalautomaten wenst ook zo aangesproken te worden) en m'n eerste ingeving, gewoon als XML behandelen met bijv. het DOM object, de elementen lezen en splitsen en in een tabel stoppen, of een vooraf bepaalde structuur CSV van maken, of xls of iest wat je wel met jullie software rechtreesk kunt behandelen.
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.
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>
Groeten,
Tony


Bericht 6 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:13-03-2008
Aan:Hugo KornelisMsgID:3771.6
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer
Hoi Hugo, en Michel, Peter en Tony,

Sorry voor het verlate bedankje (komt-ie): Enorm bedankt voor jullie input! Ik ben inmiddels aan het studeren en meld mij later vandaag of morgen met een 'echte' reactie. ;-)

Groeten,

Ronald



Bericht 7 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:13-03-2008
Aan:Michel Uphoff (Sysop)MsgID:3771.7
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Hoi Michel,
>> Je zou bijvoorbeeld die 0D0A kunnen vervangen door een teken wat wel eenvoudig wordt herkend. Geen idee of dit eenmalig is, en zo ja dan doet Uedit dit in een vloek en een zucht voor je. Je zou bijvoorbeeld de pipe | kunnen gebruiken. <<
Bedankt voor de tip! Het was inderdaad 0D0A. Het vervangen is helaas niet eenmalig. In mijn bericht aan Hugo zal ik uiteenzetten hoe ik het heb opgelost!
Groeten,
Ronald
« Laatst bewerkt op: 1 januari 2010, 17:38:08 door Ronald »

Offline Ronald

  • Forum Manager
  • *****
  • Berichten: 1.856
  • Geslacht: Man
    • NL Computer Forum
Re: XML-velden gesplitst importeren SQL2005
« Reactie #1 Gepost op: 1 januari 2010, 17:40:18 »
Bericht 8 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:13-03-2008
Aan:Hugo KornelisMsgID:3771.8
Onderwerp:XML-velden gesplitst importeren SQL2005Forum: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 A6
En 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
GO

Vervolgens 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, status

Daarna 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 STATUSTEMP

Trouwens, 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 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:13-03-2008
Aan:Tony de Jonge (Sysop)MsgID:3771.9
Onderwerp:XML-velden gesplitst importeren SQL2005Forum: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 27

NL Computer Forum ~ SQL & Programmeren
Van:Hugo KornelisDatum:14-03-2008
Aan:Ronald BeukerMsgID:3771.10
Onderwerp:XML-velden gesplitst importeren SQL2005Forum: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 :D
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 27

NL Computer Forum ~ SQL & Programmeren
Van:Hugo KornelisDatum:14-03-2008
Aan:Ronald BeukerMsgID:3771.11
Onderwerp:XML-velden gesplitst importeren SQL2005Forum: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 gemaakt
En 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 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:14-03-2008
Aan:Ronald BeukerMsgID:3771.12
Onderwerp:XML-velden gesplitst importeren SQL2005Forum: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 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:14-03-2008
Aan:Tony de Jonge (Sysop)MsgID:3771.13
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer
Hoi Tony,

Zie bericht 3771.12. Mijn antwoord aan jou kwam opeens bij mezelf terecht. <g>

Groeten,

Ronald



Bericht 14 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:14-03-2008
Aan:Hugo KornelisMsgID:3771.14
Onderwerp:XML-velden gesplitst importeren SQL2005Forum: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
Forum Manager NL Computer Forum
Microsoft Certified Solutions Expert (MCSE) - Business Intelligence

Offline Ronald

  • Forum Manager
  • *****
  • Berichten: 1.856
  • Geslacht: Man
    • NL Computer Forum
Re: XML-velden gesplitst importeren SQL2005
« Reactie #2 Gepost op: 1 januari 2010, 17:41:37 »
Bericht 15 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:14-03-2008
Aan:Hugo KornelisMsgID:3771.15
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer
Hoi Hugo,

Zeer bedankt weer voor het meedenken! :-)

>>8000 tekens vooor varchar, of 4000 voor nvarchar<<

Ik heb uiteindelijk gekozen voor Nvarchar(4000). Dat is méér dan voldoende. Die ene functie (TsqlSplit) heb ik daar ook op aangepast. Maar van REALSLOW heb ik geen last! Het gaat ook maar om maximaal een paar duizend rijen. Het XML-bronbestand is nl. per dag. Het hele proces van XML-bestand downloaden, omvormen met XVI32, importeren in SQL Server en verwerken in tabellen duurt 11 seconden. En ik hoef dit maar 1 keer per dag te laten draaien. ;-)

>>Aha! Betrapt - je hebt niet de hele site van voor tot achter gelezen!<<

Ja, ik beken. <g> En beloof beterschap!
>>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.<<

Is het een kwestie van in de inline_split_me functie varchar(8000) veranderen in nvarchar(4000), of komt er meer bij kijken? Moet er trouwens niet ergens een puntkomma in die functie staan, om de puntkomma als scheidingsteken te kunnen gebruiken? (In je andere bericht gaf je aan dat je iets had aangepast, maar dat zie ik dan even niet).

>>Zie http://sqlblog.com/blogs/hugo_kornelis/archive/2006/10/12/Stuff.aspx<<
Dank! In Google scoort deze pagina trouwens ook ér-rug goed. <g>

>>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?<<Ehrm, oeps? Dat krijg je als je te enthousiast copy/paste doet. Ik heb nu dit staan:

IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO

CREATE FUNCTION dbo.TsqlSplit
(@List As nvarchar(4000))
RETURNS @Items table (Item nvarchar(4000) Not Null)
AS
BEGIN
DECLARE @Item As nvarchar(4000), @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(','),4000)
END
RETURN
END
GO

>>Verder is het gebruik van tijdelijke tabellen helemaal niet nodig.<<
Ja, je hebt gelijk! Dat heeft te maken met hoe ik dit soort dingen maak: stapje voor stapje. Zo kan ik makkelijk zien wat er uit iedere tussenstap komt.

>>INSERT INTO #STATUSTEMP2 <<
Is dat hekje per se nodig? Ik krijg een melding dat die tabel dan niet gevonden kan worden. Als ik dezelfde query zónder hekje gebruik, dan werkt het wél.
>>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<<Oh, oeps. Ik denk dat ik nu het hekje begrijp. <g> Vooruit, ik laat dit gewoon in m'n bericht staan. Maar dan wordt mijn vraag dus: wat zou er de oorzaak van kunnen zijn dat die tijdelijke tabel bij mij niet wordt aangemaakt?

En die ORDER BY, als er een kolom is in de doeltabel die zichzelf automatisch opnummert én die volgorde (van die nummering in de doeltabel) is van belang, dán kun je toch niet zonder ORDER BY? Dat heb ik in dit geval trouwens niet hoor, dus *hier* kan de ORDER BY inderdaad wegblijven. Bedankt voor de tip, dus. ;-)

Groeten,

Ronald



Bericht 16 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Hugo KornelisDatum:15-03-2008
Aan:Ronald BeukerMsgID:3771.16
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Hoi Ronald,
Ik heb uiteindelijk gekozen voor Nvarchar(4000).
Prima. Mag ik zo brutaal zijn om te vragen waarom je unicode nodig hebt? In een bericht aan Tony heb je het over Léon, maar dat lukt met ascii en een Latin1 collation ook nog wel.
Het hele proces van XML-bestand downloaden, omvormen met XVI32, importeren in SQL Server en verwerken in tabellen duurt 11 seconden. En ik hoef dit maar 1 keer per dag te laten draaien. ;-)
Dan kan ik me voorstellen dat je kiest voor de oplossing die jij het beste begrijpt en maling hebt aan performance! :D
Is het een kwestie van in de inline_split_me functie varchar(8000) veranderen in nvarchar(4000), of komt er meer bij kijken?
Als je het netjes wilt doen, dan maak je ook van alle string constanten unicode strings, dat scheelt dan weer iets aan conversies. Dus niet 'string', maar N'string' (inderdaad, een hoofdletter N voor de eerste quote).
In Google scoort deze pagina trouwens ook ér-rug goed. <g>
Huh?Ik heb nu dit staan:

(...)
SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),4000) DATALENGTH(',') is per definitie gelijk aan 1, dus waarom niet gewoon @Pos+1 gebruiken? Of toepassen wat je op die goed scorende pagina geleerd hebt en dit vervangen door STUFF(@List, 1, @Pos, N'')?
Maar dan wordt mijn vraag dus: wat zou er de oorzaak van kunnen zijn dat die tijdelijke tabel bij mij niet wordt aangemaakt?
Goede vraag :)
Het zou ermee te maken kunnen hebben dat de tabel nog niet bestaat op het moment dat de batch gecompileerd wordt, en de opdracht die de tabel maakt (SELECT INTO of CREATE TABLE) ergens in een codepad staat waarvan de compiler niet ziet dat het eerder wordt uitgevoerd dan de query. Maar misschien is er ook wel iets heel anders aan de hand. Kan je de complete code met ons delen?
En die ORDER BY, als er een kolom is in de doeltabel die zichzelf automatisch opnummert én die volgorde (van die nummering in de doeltabel) is van belang, dán kun je toch niet zonder ORDER BY?
Als de nummering echt van belang is, zou ik niet op IDENTITY vertrouwen.
In principe is de volgorde van uitgeven vna IDENTITY waarden niet gegarandeerd hetzelfde als de ORDER BY. Nu herinner ik me een tijd geleden een blog post te hebben gelezen van één van de leden van het SQL Server team waarin wordt uitgelegd dat in één specifieke situatie (ik meen een SELECT INTO met de IDENTITY() functie in de SELECT lijst, maar pin me daar niet op vast) wél gegarandeerd wordt dat de IDENTITY waarden in de verwachte volgorde gegenereerd zullen worden, maar ik weet zo gauw geen URL. Als je het echt graag wil weten kan ik het opzoeken. In elk geval weet ik wel dat:
1) Er dus alleen in die ene situatie (waarvan ik me dus niet herinner welke het nu exact was) een garantie is; in alle andere situaties moet je het maar afwachten;
2) Dit niet in Books Online gedocumenteerd is, maar alleen in een blog van een SQL Server ontwikkelaar.

Naar mijn idee is dat een wel heel wankele basis om op te bouwen. Als ik zeker wil weten dat rijen oplopend genummerd worden, da gebruik ik ROW_NUMBER() (althans in SQL Server 2005).
Groetjes,
Hugo Kornelis, SQL Server MVP
--
Kijk ook eens op mijn blog: http://sqlblog.com/blogs/hugo_kornelis


Bericht 17 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Michel Uphoff (Sysop)Datum:15-03-2008
Aan:Ronald BeukerMsgID:3771.17
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Hoi Ronald,
Ok, ik lees dat de vervang truc werkt. Mooi.
Ik had het met Uedit opgelost (gewoon een zoek-en-vervang makrootje gemaakt), maar voornamelijk omdat ik niet de ballen verstand van SQL heb.
Terzijde, hoe is het met jouw router probleem afgelopen?

Michel Uphoff (NLcomputer)
Homepage



Bericht 18 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Tony de Jonge (Sysop)Datum:19-03-2008
Aan:Ronald BeukerMsgID:3771.18
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Ronald,
Allereerst, dat bestand van je is geen XML, een XMl document heeft namelijk op het hoogste niveu altijd slechts 1 element, je had er twee... (maar ik neem even aan dat dat door het handmatig bewerken is gekomen?)
Er zijn twee richtingen die ik zou bewandelen (al heb ik er slecht 1 geprobeerd). De oplossing die ik toch niet meer heb uitgeprobeerd is gebruik te maken van het zogenoemde DOM object, dat is een XML document object waarbij het XML bestand (maar dan moet het wel een goed bestand zijn anders krijg je een foutmelding) in een objectstructuur beschikbaar komt. Vergt wat programmeer werk.
Een andere, voor mij nieuwe maar wel leuke manier is door gebruik te maken van LogParser, sinds kort heb ik die tool ontdekt en ik moet zeggen, een zeer handig en krachtig hulpmiddel (zoek bij microsoft op Log Parser 2.2) waarmee je o.a. XML kunt omzetten naar CSV etc. en wat bewerkingen op los kunt laten, bijv. die CR/LF omzetten naar een ander teken dat later eenvoudig is te gebruiken voor het splitsen van de inhoud. Maar nog veel meer leuke dingen mee kunt doen, als ik noor voor een computerbad zou schrijven zou ik daar zeker een artikel over gaan maken... (je kunt er zelfs grafieken etc. mee maken...)
Groeten,
Tony


Bericht 19 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:26-03-2008
Aan:Michel Uphoff (Sysop)MsgID:3771.19
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer
Hoi Michel,

>>Ik had het met Uedit opgelost (gewoon een zoek-en-vervang makrootje gemaakt)<<

Wat ik mij nog afvroeg: kun je daar dan iets van maken dat in (of met behulp van) een batch bestandje kan worden uitgevoerd? En moet je dan ook UltraEdit hebben op de machine waar e.e.a. draait?

Groeten,

Ronald



Bericht 20 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:26-03-2008
Aan:Hugo KornelisMsgID:3771.20
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer
Hoi Hugo,

Wederom mijn enorme dank! Ik was even druk met andere projecten, maar inmiddels mag ik me weer hierin vastbijten. <g>

Wat die unicode betreft: helaas komen er op allerlei manieren data in het bronsysteem (waar die XML dan weer een output van is). Ook leveranciers stoppen er data in, en soms doen ze dat niet zo netjes. Dan staat er opeens een 'raar' teken in. En dan kan het gebeuren dat de import van het XML-bestand in SSIS foutloopt (wordt afgebroken).

Wat mij nu niet duidelijk is: is nvarchar nu iets anders dan Nvarchar? Ik had die "N" er vanwege de duidelijkheid voorgezet, dus als het daardoor een andere betekenis heeft gekregen: ehrm, oeps? <g>  Ik had eerst varchar gebruikt, totdat jij in bericht 3771.4 schreef:

(Overigens, afhankelijk van wat er exact in de data kan staan moet je misschien Nvarchar in plaats van varchar gebruiken - XML data is per definitie unicode, en bij de conversie naar 8-bit ASCII kan informatie verloren gaan).

Nou, als jij dat zegt, dan doe ik dat natuurlijk meteen! <bg>

>>Of toepassen wat je op die goed scorende pagina geleerd hebt en dit vervangen door STUFF(@List, 1, @Pos, N'')?<<

Is gedaan! En het werkt nog ook... ;-)

>>Nu herinner ik me een tijd geleden een blog post te hebben gelezen van één van de leden van het SQL Server team waarin wordt uitgelegd dat in één specifieke situatie (ik meen een SELECT INTO met de IDENTITY() functie in de SELECT lijst<<

Ik gebruik dus een INSERT INTO, mag dat ook? ;-)  De tabel waar het allemaal ingaat heeft een 'ID' kolom met de IDENTITY eigenschap. Ik gooi de tabel leeg met TRUNCATE TABLE vóór dat ik 'm opnieuw ga vullen (dus ID begint te tellen bij 1). Ik heb nog niet kunnen vaststellen dat het fout gaat!

>>
Als ik zeker wil weten dat rijen oplopend genummerd worden, dan gebruik ik ROW_NUMBER() (althans in SQL Server 2005)<<

<engelachtige blik>Heb je daar een klein voorbeeldje van?
</engelachtige blik>

Trouwens, zou je een blik kunnen werpen op dit bericht? Ik heb de naam van deze berichtensectie inmiddels stiekem aangepast.... ;-)

Groeten,

Ronald



Bericht 21 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:26-03-2008
Aan:Tony de Jonge (Sysop)MsgID:3771.21
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer
Hoi Tony,


>>Allereerst, dat bestand van je is geen XML, een XMl document heeft namelijk op het hoogste niveu altijd slechts 1 element, je had er twee... (maar ik neem even aan dat dat door het handmatig bewerken is gekomen?)<<Klopt! In het echte XML-bestand is dat wel goed geregeld. <g>

LogParser heb ik gevonden en ga ik meteen bekijken. Bedankt voor de tip! :-)

Groeten,

Ronald




Bericht 22 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Michel Uphoff (Sysop)Datum:26-03-2008
Aan:Ronald BeukerMsgID:3771.22
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Hoi Ronald,
>> En moet je dan ook UltraEdit hebben op de machine waar e.e.a. draait? <<
Als je van de uEdit macro's gebruik maakt, moet dat inderdaad. Je zou zo'n makrootje als autostart macro kunnen definiëren. Maar dat lijkt mij een heel gedoe zo.
Als het een vast terugkerende routine is, is het soms handig er een kleine .exe voor te maken. Met VB kan je ook prima parsen en strings editen. Ik heb natuurlijk geen idee om hoeveel data het gaat en wat er exact moet gebeuren. Maar als er te veel (dagelijkse) handelingen nodig zijn om die data om te timmeren met de geëigende gereedschappen, of die gereedschappen vereisen veel kennis/geld, dan kon een speciaal tooltje wel eens beter zijn.
Op mijn werk hadden wij ook zo'n probleem: Er moesten maandelijk 350.000 regels data worden omgezet. De applicatiebeheerder had daar een behoorlijk complexe setup in Access voor gemaakt, met als enorm nadeel dat alleen hij het kon en het bovendien traag als dikke (nou ja je weet wel), de computer was er meer dan een half uur mee bezig.
Het bleek in feite te gaan om wat vervangingen, weglatingen en wat layout. In VB was het in een uurtje geprogrammeerd, geautomatiseerd, en de doorlooptijd was 4 minuten.
Als je het echt kan gebruiken, en het is niet complex, wil ik best wel eens even voor je kijken of zoiets vast te programmeren is.


Michel Uphoff (NLcomputer)
Homepage
Forum Manager NL Computer Forum
Microsoft Certified Solutions Expert (MCSE) - Business Intelligence

Offline Ronald

  • Forum Manager
  • *****
  • Berichten: 1.856
  • Geslacht: Man
    • NL Computer Forum
Re: XML-velden gesplitst importeren SQL2005
« Reactie #3 Gepost op: 1 januari 2010, 17:42:23 »
Bericht 23 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Hugo KornelisDatum:26-03-2008
Aan:Ronald BeukerMsgID:3771.23
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Hoi Ronald,
Wat mij nu niet duidelijk is: is nvarchar nu iets anders dan Nvarchar?
Nee, dat is exact hetzelfde. Keywords zijn in SQL Server nooit case sensitive. Je mag dus ook nVaRChaR gebruiken, als je dat leuk vindt :D
Ik had die "N" er vanwege de duidelijkheid voorgezet, dus als het daardoor een andere betekenis heeft gekregen: ehrm, oeps? <g>  Ik had eerst varchar gebruikt, totdat jij in bericht 3771.4 schreef:

(Overigens, afhankelijk van wat er exact in de data kan staan moet je misschien Nvarchar in plaats van varchar gebruiken - XML data is per definitie unicode, en bij de conversie naar 8-bit ASCII kan informatie verloren gaan).

Nou, als jij dat zegt, dan doe ik dat natuurlijk meteen! <bg>

Dus het is allemaal weer mijn schuld? :-P
Als de "rare" tekens in dat bestand Cyrillisch, Chinees, Turks, of Klingon kunnen zijn en ze op die manier behouden moeten worden, dan heb je inderdaad unicode nodig. Als alle tekens in het bestand uit de 8-bits ASCII set komen, heb je geen unicode nodig.
Unicode kost twee bytes per teken, niet-unicode slechts één byte per teken. Voor unicode variabelen en kolommen gebruik je datatypes nchar en nvarchar. Voor niet-unicode is dat char en varchar. Voor string constanten zet je een hoofdletter N voor de eerste apostrof om de constante als unicode te declareren (dit is vergelijkbaar met hoe ik ooit in een oud basic dialect de getal constante 7, 7e0 of 7d0 gebruikte voor integer, enkele precisie respectievelijk dubbele precisie).
Dus als variabele @a een varchar is, dan gaat SET @a = 'Hello world' zonder conversie, en SET @a = N'Hello world' met (iompliciete) conversie. Is @a gedeclareerd als nvarchar, dan is dat precies andersom. Omdat conversies vaak slecht kunnen zijn voor de performance, is het een goede gewoonte om string constanten door toevoegen of weglaten van die N het juiste data type te geven.
>>Nu herinner ik me een tijd geleden een blog post te hebben gelezen van één van de leden van het SQL Server team waarin wordt uitgelegd dat in één specifieke situatie (ik meen een SELECT INTO met de IDENTITY() functie in de SELECT lijst<<

Ik gebruik dus een INSERT INTO, mag dat ook? ;-)  De tabel waar het allemaal ingaat heeft een 'ID' kolom met de IDENTITY eigenschap. Ik gooi de tabel leeg met TRUNCATE TABLE vóór dat ik 'm opnieuw ga vullen (dus ID begint te tellen bij 1). Ik heb nog niet kunnen vaststellen dat het fout gaat!

Maar dat is nu juist de ellende met ongedefinieerde resultaten - ze kúnnen zomaar per ongeluk correct zijn...
Ik heb intussen even wat gezocht en het blijkt dat mijn geheugen me in de steek heeft gelaten. De SELECT INTO met de IDENTITY() functie kan juist onverwachte resultaten opleveren, omdat de IDENTITY waarden worden bepaald voordat de sortering plaatsvindt. Dit wordt beschreven in dit MSKB artikel: http://support.microsoft.com/kb/273586. In dat artikel staat ook dat "INSERT INTO een bestaande tabel SELECT de waarden ORDER BY gewenste volgorde" juist wel werkt, al blijf ik het een enge feature vinden om op te vertrouwen.
>>Als ik zeker wil weten dat rijen oplopend genummerd worden, dan gebruik ik ROW_NUMBER() (althans in SQL Server 2005)<<

<engelachtige blik>Heb je daar een klein voorbeeldje van?
</engelachtige blik>
Je weet dat ik je niets kan weigeren als je zo kijkt... :)INSERT INTO DoelTabel (Kolom1, Kolom2, NummerKolom
SELECT Kolom1, Kolom2,
       ROW_NUMBER() OVER (ORDER BY Kolom1, Kolom2)

FROM   Herkomst;
Groetjes,
Hugo Kornelis, SQL Server MVP

--
Kijk ook eens op mijn blog: http://sqlblog.com/blogs/hugo_kornelis


Bericht 24 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:01-04-2008
Aan:Hugo KornelisMsgID:3771.24
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer
Hoi Hugo,

>> Dus het is allemaal weer mijn schuld? :-P <<

LOL  Nee hoor, eerder mijn schuld: 'oh Hugo zegt dat, dus dan zal ik dat ook maar doen!' ;-)

>>Als de "rare" tekens in dat bestand Cyrillisch, Chinees, Turks, of Klingon kunnen zijn en ze op die manier behouden moeten worden, dan heb je inderdaad unicode nodig. Als alle tekens in het bestand uit de 8-bits ASCII set komen, heb je geen unicode nodig.<<Wat dacht je van het euro-teken? Daar is toch wel unicode voor nodig?
Dank je voor de uitleg over n/Nvarchar, helemaal duidelijk zo!
>>Dit wordt beschreven in dit MSKB artikel: http://support.microsoft.com/kb/273586. In dat artikel staat ook dat "INSERT INTO een bestaande tabel SELECT de waarden ORDER BY gewenste volgorde" juist wel werkt, al blijf ik het een enge feature vinden om op te vertrouwen.<<

Phew, dan heb ik in ieder geval de goede methode gekozen! Met ROW_NUMBER zal ik ook proberen, misschien werkt het sneller... ;-)

Groeten,

Ronald


Bericht 25 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Michel Uphoff (Sysop)Datum:01-04-2008
Aan:Ronald BeukerMsgID:3771.25
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Hoi Ronald,
>> Wat dacht je van het euro-teken? Daar is toch wel unicode voor nodig? <<
Het euroteken zit in de zgn. extended ascii reeks (Alt-0128). Nu is die extended set niet echt vastgesteld, dus of ieder font werkt is de vraag. Maar de meeste 'standaard' Windows fonts doen dat prima.

Michel Uphoff (NLcomputer)
Homepage



Bericht 26 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:02-04-2008
Aan:Michel Uphoff (Sysop)MsgID:3771.26
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Hoi Michel,
>> De applicatiebeheerder had daar een behoorlijk complexe setup in Access voor gemaakt, met als enorm nadeel dat alleen hij het kon en het bovendien traag als dikke (nou ja je weet wel), de computer was er meer dan een half uur mee bezig.<<
  Zeker een mdb-bestand van 2 GB ook? Ik zal geen namen noemen, maar ik ben bij een bedrijf zoiets tegengekomen (en het was nog een bedrijfskritisch ding ook, waar ze dan met meerdere personen tegelijkertijd in zaten te werken).
>>Als je het echt kan gebruiken, en het is niet complex, wil ik best wel eens even voor je kijken of zoiets vast te programmeren is. <<
Hartelijk dank voor het aanbod! Op dit moment heb ik het met XVI32 goed opgelost: het werkt snel en kost niets. Maar nogmaals zeer bedankt voor het aanbod!
Groeten,
Ronald (Sysop)
 
 


Bericht 27 van 27

NL Computer Forum ~ SQL & Programmeren
Van:Ronald BeukerDatum:02-04-2008
Aan:Michel Uphoff (Sysop)MsgID:3771.27
Onderwerp:XML-velden gesplitst importeren SQL2005Forum:ws-nlcomputer

Hoi Michel,
>> Het euroteken zit in de zgn. extended ascii reeks (Alt-0128).  <<
Aah zo! Dan heeft de melding die Outlook Express je geeft zodra je iets met een € teken erin probeert te sturen mij op het verkeerde ben gezet (dan krijg je nl. de vraag of Outlook Express het 'gewoon' of als unicode moet verzenden. Ik heb gemerkt dat je, als je kiest voor 'gewoon' versturen, je dan het euro-teken kwijt bent.
Ik laat het maar op unicode staan. Het is nu geen bottleneck en er zitten soms echt vreemde tekens in dat bronbestand.
Groeten,
Ronald
Forum Manager NL Computer Forum
Microsoft Certified Solutions Expert (MCSE) - Business Intelligence