how to make this query?

Hello,

I have a table structure like this where TB1 ... TD9 represent pks and fks

Now, I would like to know for TABLEA - TA1, how many TDs exist. Similarily for any other primary key in TableA. Can we write a query to do that? Shall appreciate your ideas.

Thanks in advance.

TABLEA TA1 TB1
TB2
TABLEB TB1 TC1
TC2
TB2 TC3
TC4

TABLED TC1 TD2
TD3
TC2 TD4
TD5
TC3 TD6
TD7
TC4 TD8Hello,

I have a table structure like this where TB1 ... TD9 represent pks and fks

Now, I would like to know for TABLEA - TA1, how many TDs exist. Similarily for any other primary key in TableA. Can we write a query to do that? Shall appreciate your ideas.

Thanks in advance.

TABLEA TA1 TB1
TB2
TABLEB TB1 TC1
TC2
TB2 TC3
TC4

TABLED TC1 TD2
TD3
TC2 TD4
TD5
TC3 TD6
TD7
TC4 TD8|||Sounds like you just want to do this:

select ta1, count(*)
from tablea
group by ta1;

I don't understand this part of your question though: "Similarily for any other primary key in TableA", because a table only has one primary key, and a primary key value will always appear in exactly one row.|||In table a TA1 has TB1 and TB2 as foreign keys.
TB1 has TC1 and TC2. TC1 has TD2 TD3. This grows like a tree ....

Now, for TA1, the query should output TD2 .. TD8

That means for a given key of first table, we have to get all the keys of last table (foreign keys that exist in last but one table)|||A simple query is below. (This was just to get the results)

One expected / correct method to achieve this is to use a PL/SQL procedure that implements recursion. I would also recommend having a single table containing the PK-FK relationships.

tableA

C1 C2
---- ----
TA1 TB1
TA1 TB2

TableB and TableC follow the same structure

SQL> select tableC.c2
2 from tableA, tableB, tableC
3 where tableA.c2 = tableB.c1 AND
4 tableC.c1 = tableB.c2 AND
5 tableA.c1 = 'TA1';

C2
----
TD2
..
TD8sql

how to load a set of 10000 xml files to a sql table?

Hi all,
I have a folder with about 10.000 xml files that need to be transferred to a
SQL table.
the structure of the xml file is something like this:
<?xml version="1.0" encoding="UTF-8"?>
<customer>
<id>123</id>
<sex>M</sex>
<firstName>firstname</firstName>
<lastName>Lastname</lastName>
<phone>123123</phone>
<BillingAddress>
<id>23</id>
<street1>Street1</street1>
<street2></street2>
<postalCode>12312</postalCode>
<city>City</city>
<country>DE</country>
</BillingAddress>
<ShippingAddress>
<id>23</id>
<street1>Street1</street1>
<street2></street2>
<postalCode>12312</postalCode>
<city>City</city>
<country>DE</country>
</ShippingAddress>
</customer>
How can I do this?
Has anyone a sample or a brief description how to do this?
Thank you very much!
Regards, Martin
Martin,
I have just written code for an ACTIVEX controled DTS that does exactly
what you are looking for. Ive copied and pasted the code below. Im just a
jr. developer, but I hope this helps you. You will have to change the code
so that it can "read" your xml files. Also, ive added some links to sites
that helped me accomplish this.
Ben
Links (sorry, i dont know how to do HTML links) (the second link is really
good):
http://www.eggheadcafe.com/articles/20030627b.asp
http://www.perfectxml.com/articles/xml/importxmlsql.asp
my code:
'************************************************* ***********
' Visual Basic ActiveX Script
'************************************************* ***********
Option Explicit
Function Main()
' Declare FSO Related Variables
Dim sFolder
Dim fso
Dim fsoFolder
Dim fsoFile
Dim sFileName
' Import Folder
'global variable "ImportFolder" has a value like: C:\logs\
sFolder = DTSGlobalvariables("ImportFolder")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
'reads each file in the folder and moves them to a second
folder
For Each fsoFile in fsoFolder.Files
sFileName = sFolder & fsoFile.Name
call ParseFile(sFilename)
call MoveFile(sFilename)
Next
Main = DTSTaskExecResult_Success
End Function
'************************************************* **********
' ParseFile
' This method takes as input the complete name of an xml file
' (including path) and retrieves specific information from the
' file, as needed for the Webshield SPAM reporting
'************************************************* ***********
Function ParseFile(importFile)
'Declare File Related Variables
Dim objXMLDOM
Dim objNodes
Dim objEventNode
Dim objADORS
Dim objADORS2
Dim objADOCnn
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False
'No error handling done
objXMLDOM.load importFile
'setting the format of the xml files, the eventlog has nodes
called event
Set objNodes = objXMLDOM.selectNodes("/EventLog/Event")
Set objADOCnn = CreateObject("ADODB.Connection")
Set objADORS = CreateObject("ADODB.Recordset")
Set objADORS2 = CreateObject("ADODB.Recordset")
objADOCnn.Open
"PROVIDER=SQLOLEDB;SERVER=(local);UID=sa;PWD=;DATA BASE=Northwind;"
objADORS.Open "SELECT * FROM Logs", objADOCnn, adOpenKeyset, adLockOptimistic
objADORS2.Open "SELECT * FROM Imported", objADOCnn, adOpenKeyset,
adLockOptimistic
'Record file that was imported
With objADORS2
.AddNew
.fields("FileName") = importFile
.Update
End With
'Import record(s)
For Each objEventNode In objNodes
With objADORS
.AddNew
'read an attribute of the
event
.fields("Date")=
objEventNode.selectSingleNode("@.local-time").nodeTypedValue
'read specific values of the
event
.fields("From Inside Total") =
objEventNode.selectSingleNode("Info[@.name='smtp.fr om_inside.messages']").nodeTypedValue
.Update
End With
Next
objADORS.Close
objADOCnn.Close
ParseFile = DTSTaskExecResult_Success
End Function
'************************************************* **********
' MoveFile
' This method moves a file from one folder to another so that the
' file does not get imported into the SQL database twice
'************************************************* **********
Function MoveFile(importFile)
' Declare FSO Related Variables
Dim sFolder
Dim endFolder
Dim fso
Dim fsoFolder
' Import Folder
sFolder = DTSGlobalvariables("ImportFolder")
' Imported Files Folder
endFolder = DTSGlobalvariables("saveFolder")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
'move the file
fso.movefile importFile, endFolder
MoveFile = DTSTaskExecResult_Success
End Function
|||Hi Ben,
"Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
news:996476C5-8CC6-4444-8AC8-33FE3C601403@.microsoft.com...
> Martin,
> I have just written code for an ACTIVEX controled DTS that does exactly
> what you are looking for. Ive copied and pasted the code below. Im just
> a
> jr. developer, but I hope this helps you. You will have to change the
> code
> so that it can "read" your xml files. Also, ive added some links to sites
> that helped me accomplish this.
> Ben
Thank you very much! I think this was exactly what I was searching for.
Just one more question:
Do you start the code with a ActiveX Control in a dts package as described
in
http://www.perfectxml.com/articles/x...rtxmlsql.asp#7 ?
Regards, Martin
|||Martin,
I'm not sure I understand your question. The following are the steps to
use to be able to use the code:
1. In enterprise manager, under the specific server you are using, click on
data transfer services and then right click in the right window and create a
new package
2. Right click -> add task -> activeX script task
3. Copy in the code and then modify for your xml files
To set the global variables i use,
close the tasks and make sure non are selected, click package -> properties
and then click the tab global variables.
i hope this answers your question. if not, could you please rephrase it?
thanks.
Ben
"Martin Bübl" wrote:

> Hi Ben,
> "Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
> news:996476C5-8CC6-4444-8AC8-33FE3C601403@.microsoft.com...
>
> Thank you very much! I think this was exactly what I was searching for.
> Just one more question:
> Do you start the code with a ActiveX Control in a dts package as described
> in
> http://www.perfectxml.com/articles/x...rtxmlsql.asp#7 ?
> Regards, Martin
>
>
|||Hi Ben,
thank you, again this was exactly the information I was asking you for.
Regards, Martin
"Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
news:C3B288ED-D170-4465-97B1-62EA7AAD0086@.microsoft.com...[vbcol=seagreen]
> Martin,
> I'm not sure I understand your question. The following are the steps
> to
> use to be able to use the code:
> 1. In enterprise manager, under the specific server you are using, click
> on
> data transfer services and then right click in the right window and create
> a
> new package
> 2. Right click -> add task -> activeX script task
> 3. Copy in the code and then modify for your xml files
> To set the global variables i use,
> close the tasks and make sure non are selected, click package ->
> properties
> and then click the tab global variables.
> i hope this answers your question. if not, could you please rephrase it?
> thanks.
> Ben
> "Martin Bbl" wrote: