Verwijderd schreef op dinsdag 07 december 2010 @ 18:28:
Aangezien je niet weet hoe lang de data is kun je toch gewoon het datatype 'text' gebruiken, waarbij je geen lengte hoeft te definiëren, zodat je alsnog alles kunt concatten en vervolgens doorzoeken?
Misschien een erg naïeve oplossing, maar wel eenvoudig en werkbaar.
Maar de diepte is ook onbekend
Dus de filename kan op seq_num = 2 staan, maar ook op seq_num = 23.
Daarnaast kan ie ook nog eens afgebroken staan op twee seq_num's. Daarvoor is de oplossing om een hele lange string te maken, maar is voor mij de onbekende diepte een probleem.
Ja lach maar!
Het is voor documentatie doeleinden. Dus automatisch genereren van een hoop technische gegevens die handmatig te veel tijd gaan kosten. Dan kan er meer tijd worden gestoken in het functioneel documenteren van de ETL logica.
Het gaat hier overigens om het querien op een repository van een ETL tool. Normaal gesproken heeft niemand iets te zoeken in de repository en laat je de applicatie haar truukjes uitvoeren zoals ze dat zelf wel. Ik vermoed overigens dat de applicatie in een grijs verleden ooit niet op een db gebaseerd was. Maar goed, ik ben allang blij dat het human readable is.
Maar heb je binnen 1 object_key niet 1 "xml document" te pakken?
Dus is het niet zo:
code:
1
2
3
4
5
6
7
8
9
| OBJECT_KEY SEQ_NUM TEXT_VALUE
1 1 <OBJECT="OBJECTNAAM ID="GUID"><OPTIONS>VEELOPTIE
1 2 SNOGMEEROPTIESETC
1 3 filename="Excelsheet.xls"</OPTIONS>
1 4 </OBJECT>
2 1 <OBJECT="OBJECTNAAM2 ID="GUID2"><OPTIONS>VEELOPTIE
2 2 S2NOGMEEROPTIESETC
2 3 filename="Excelsheet2.xls"</OPTIONS>
2 4 </OBJECT> |
?
Correct. Sorry dat ik deze informatie niet heb gegeven.
De combinatie OBJECT_KEY en SEQ_NUM maakt het uniek. Één OBJECT_KEY is één xml-object.
In dat geval kun je een enkel object ophalen met iets als:
SQL:
1
2
3
4
| select ...
from mytable
where object_key = 1
order by seq_num |
En dan door de records heen wandelen en de string concatten. Wil je meerdere objecten ophalen dan pas je de where aan met een "where object_key in (1, 2, ...)" of als je alles wil hebben laat je de where achterwege.
Er zijn wel meerdere objecten waar ik de gegevens van nodig heb, maar daar heb ik een aparte query voor die de resultaatset limiteert tot de objecten die specifiek nodig zijn. Dus inderdaad een IN() statement.
In elk geval rag je de resultaatset van de query door een parsedocuments functie:
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| function parsedocuments(records) {
currentkey = null
mydocument = ''
foreach records as r {
if currentkey != r.object_key {
parsedocument(mydocument)
currentkey = r.object_key
}
mydocument += r.text_value
}
parsedocument(mydocument)
}
function parsedocument(xmlstring) {
if (xmlstring != null) {
//Parse XML string here.
}
} |
Ik ben (nog) niet thuis in functies... maar ik ontkom er niet aan vanwege de onbekende hoeveelheid SEQ_NUM's...
Zie vorige opmerking; dat zou betekenen dat je bij elke nieuwe object_key die je tegen komt een nieuw document kunt parsen (en dus binnen 1 object_key de strings concatten).
Correct.
En in hoeverre heb je de garantie dat "filename=" niet/wel voorkomt in "VEELOPTIESNOGMEEROPTIESETC" of elders in de tekst? Want je maakt aannames die je (toch al kazige) oplossing (waar je dan zelf niet schuld aan bent weliswaar) nog een behoorlijk stuk fragieler maakt.
Daar ben ik me bewust van. Ik moet dit controleren, maar een aantal spot checks laten zien dat er maar één filename is. Als er meerdere zijn, zijn deze wel allemaal relevant. Maar ik dacht dat er in dit object maar één was toegestaan, maar zal ik nog controleren.
Lijkt me weinig relevant en sowieso niet een factor waar je op wil vertrouwen.
Je ontkomt, IMHO, niet aan het fatsoenlijk parsen van het (bijeengesprokkelde en daarna met plakband bijeen geprakte) XML document. Een DB-onafhankelijke manier van deze meuk uitspitten in SQL statements alléén lijkt me haast onmogelijk (en anders gaat er een shitload aan tijd in zitten om 't goed/betrouwbaar te krijgen). Met MSSQL (of andere RDBMS) specifieke functies kom je nog een heel eind maar dan nog kom je de flarden XML als een 'heel document' aan 't benaderen wil je 't betrouwbaar hebben lijkt me.
In die zin is er wel op te vertrouwen dat als dit niet klopt, de ETL code invalide is en niet meer draait. Dan is het automatisch documenteren het kleinste probleem...
Geef jij SAP even een trap dan
Vooraleerst: de simpelste oplossing is denk ik het gebruik van een function based view. Ik weet alleen niet of je zomaar een typecasting zou kunnen doen van een hele zut aan geconcateneerde string values naar een long, maar dat kun je uitproberen
En dan met subs en instr op die long zaken ophalen (gaat ook niet zomaar, maar daar zijn wel trucjes voor te vinden
Desnoods icm een stored procedure die gaat zoeken in een long op een string en die weer in een view hangen oid
Wordt het begin van de filename aangeduid met een " of met filename=" ? Dat zou je sowieso al eens kunnen helpen, omdat je dan begin en einde uniek kunt identificeren binnen een gecombineerde sleutelwaarde van OBJECT_KEY en SEQ_NUM.
Met:
filename="
Verder moet het begin van de filenaam zo te zien OF in dezelfde seqnum, OF in een andere seqnum van dezelfde object key zitten. Dat helpt ook met het maken van je where-clause. Dat wordt een dubbele EXISTS zo te zien
Dat je de waarde van de op te halen object_key's en seq_num's moest bepalen met die instr en substr had je vast zelf ook al gevonden
Dat weet ik nog net wel
Filename kan ook als volgt zijn afgekapt, het wordt echt bruut gedaan:
code:
1
2
3
| SEQ_NUM TEXT_VALUE
22 optiesoptiesohisteinderegelmaareerstnogbeginvande...fil
23 ename=" |
Btw, welk pakket is dit? Neigt naar PeopleSoft of iig een Oracle smaak namelijk
SAP Business Objects Data Services (BODS) om te precies te zijn, voormalig Business Objects Data Integrator (BODI). OWB genereert redelijke documentatie, maar BODS/BODI genereert wel wat, maar nooit echt precies wat je zelf wil.
Overigens is er een view in de repository welke datalineage weergeeft, dus van brontabel + bron attribuut naar target tabel + target attribuut, zelfs als het samengestelde attributen etc worden. Daar werd ik wel enthousiast van, want op basis hiervan kan je een datalineage generen. Maar goed, dat is een uitdaging voor later aangezien eerst de basis van de automatische documentatie moet staan.
In principe zelden en ook nog eens zelden op productie. Na development kan de documentatie dan automatisch gegenereert worden per release.
maar een quick en dirty oplossing zou de volgende functie kunnen zijn :
SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| CREATE FUNCTION dbo.GetObjectTextValue
(
@ObjectKey int
) RETURNS nvarchar(500)
AS
BEGIN
DECLARE @ObjectTextValue nvarchar(500)
SET @ObjectTextValue = '' -- set initial value to avoid concatting NULL
SELECT @ObjectTextValue = @ObjectTextValue + TEXT_VALUE
FROM SourceTable t
WHERE OBJECT_KEY = @ObjectKey
ORDER BY SEQ_NUM
DECLARE @Position int
SET @Position =PATINDEX('%filename="%', @ObjectTextValue) -- get filename start
SET @ObjectTextValue = SUBSTRING( @ObjectTextValue, @Position+10, LEN(@ObjectTextValue)-@Position+11) -- strip left side
SET @Position =PATINDEX('%"%', @ObjectTextValue) -- get end quote
SET @ObjectTextValue = SUBSTRING( @ObjectTextValue, 1,@Position - 1) -- strip right side
RETURN @ObjectTextValue
END |
Om op te halen :
SQL:
1
2
3
| -- Retrieve Results
SELECT DISTINCT OBJECT_KEY, dbo.GetObjectTextValue(OBJECT_KEY) AS FileName
FROM SourceTable |
levert :
code:
1
2
3
| OBJECT_KEY FileName
1 Excelsheet1.xls
2 Excelsheet2.xls |
Hierbij ben ik ervanuit gegaan dat die filename="Excelsheet1.xls" uniek is binnen de hele dataset per OBJECT_KEY. Is dit niet het geval zal je de functie wat complexer moeten maken.
Dit wordt niet afgedwongen, maar als dit niet uniek is, dan is een file locking probleem omdat er tegelijkertijd naar hetzelfde bestand wordt weggeschreven. Dit kan dus in theorie voorkomen als er meerdere files per object zijn toegestaan (moet ik dus nog controleren), maar levert operationeel gezien problemen op.
In ieder geval dank voor het voorbeeld. Het is meer dan ik had verwacht (je moet tenslotte zélf zoeken
), maar ik wist echt niet waar ik moest beginnen.
Nogmaals, dit is quick en dirty en niet bedoeld om op grote tabellen heel frequent uit te voeren zonder goed te weten wat de impact op je performance is.
Tabel is niet groot, heeft index op OBJECT_KEY en SEQ_NUM en ik limiteer met een andere query specifiek alleen op de objecten die ik nodig heb. Desnoods is er een kopie van de tabel te maken naar een andere db zodat er geen impact is op de performance. Gelukkig is performance impact op een operationeel systeem niet het probleem.
Allen, dank voor de antwoorden. Ik ga hier donderdag mee verder en kan ik of trots melden dat het gelukt is met eeuwig dank, of geef ik aan waar ik ben vastgelopen en wat ik heb uitgezocht
:strip_exif()/u/107011/Motrax.gif?f=community)
:strip_icc():strip_exif()/u/43400/avatar.jpeg?f=community){kind=avatar}
:strip_icc():strip_exif()/u/46486/RayBan.jpg?f=community)