XQuery help

[Fuma]

So I have a table called (CompanyDetails) with an XML column. The column contains data like this:

<Companies>
	<Company>
		<CompanyID>100</ComapnyID>
		<Employees>
			<Employee [COLOR="#FF0000"]EmployeeID="1"[/COLOR]>
				<Name>David</Name>
				<Surname>Smith</Surname>
				<Loans>
					<Loan>
						<Amt>100</Amt>
						<Term>12</Term>
					</Loan>
					<Loan>
						<Amt>500</Amt>
						<Term>24</Term>
					</Loan>
				</Loans>
			</Employee>
			<Employee [COLOR="#FF0000"]EmployeeID="2"[/COLOR]>
				<Name>Donald</Name>
				<Surname>Jones</Surname>
				<Loans>
					<Loan>
						<Amt>300</Amt>
						<Term>12</Term>
					</Loan>
					<Loan>
						<Amt>2000</Amt>
						<Term>36</Term>
					</Loan>
				</Loans>
			</Employee>
		</Employees>

	</Company>
</Companies>

I need to extract data from this column and insert into different tables (employee, employeeLoan, etc).
I wrote a console app to add the EmployeeID attribute (it wasn't there) in the Employee element based on the the total number of employees in the CompanyDetails table

UPDATE CompanyDetails
SET XMLDATA.modify('insert attribute EmployeeID {sql:variable(""@EmployeeID"")} into (/*:Companies/*:Company/*:Employees/*:Employee[position()=sql:variable(""@NodeNr"")])[1]')
WHERE CompanyID = @CompanyID

This works fine, but as soon as I have a lot of data in the CompanyDetails table, it is unbearably slow.

What's the best way to insert into the employee and employeeLoan tables (EmployeeID column being the PK and FK in the two tables respectively)? Any ideas?

 

[Necropolis]

Why the XML column in the first place?

Would it not be quicker to write an app to parse the XML strings and do the inserts that you want?

 

[Fuma]

Why the XML column in the first place?

Would it not be quicker to write an app to parse the XML strings and do the inserts that you want?

My CompanyDetails table is an import from another table (from another server and RDBMS) than has an XML column. I have no control over the source table.

 

[[)roi(]]

Hard to advise with out a clearer picture of the end to end process.

If it's between two data sources then XML would be very inefficient; however if it's host -> client, then Necropolis suggestion sounds on point.

 

[Fuma]

Hard to advise with out a clearer picture of the end to end process.

If it's between two data sources then XML would be very inefficient; however if it's host -> client, then Necropolis suggestion sounds on point.

We have a daily batch job that imports data from DB2 (table with XML column) to SQL Server (CompanyDetails) through a console app (SqlBulkCopy). We have an older version of BITS (SSIS) which does not support XML columns. The import still takes a long time due to the XML column (the xml content is huge).

After the import to SQL Server, I need to then extract the data from CompanyDetails to employee, employeeLoan, etc.

I suppose I could manipulate the XML column data as I import.

 

[[)roi(]]

We have a daily batch job that imports data from DB2 (table with XML column) to SQL Server (CompanyDetails) through a console app (SqlBulkCopy). We have an older version of BITS (SSIS) which does not support XML columns. The import still takes a long time due to the XML column (the xml content is huge).

After the import to SQL Server, I need to then extract the data from CompanyDetails to employee, employeeLoan, etc.

I suppose I could manipulate the XML column data as I import.

Direct connections are generally the preferred option with ETLs.

If SSIS, is not adequate, then you could try an opensource alternative e.g. Talend ETL. Basically you design your transforms in a similar fashion to SSIS, and it code generates the Java to do this (incl. parallelisation). In simple terms you end up with a Java jar, which you deploy to run somewhere appropriate.