Link to home
Start Free TrialLog in
Avatar of Si Ball
Si BallFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Take xml String in SQL2000 and convert it inot an append/insert statement

As per my other question, i want to turn the parent part of my parent and child xml record into an append query to append to a table with the same columns names as my xml fields...

is there a nice tidy quick loop way of going through the XML nodes and making a database record?

e.g.

<record>
<QLXID></QLXID>
<QLSID>DMU</QLSID>
<GeneralLedgercode>13.X.XXX.1.432</GeneralLedgercode>
<Customerfirstname>Sudo</Customerfirstname>
<Customersurname>Nim</Customersurname>
<PaymentMethod>D</PaymentMethod>
<CardHolderName>Sudonim</CardHolderName>
<WPAYDOM_DDMM>WPAYDOM_2901</WPAYDOM_DDMM>
<PaymentType>STORE</PaymentType>
<GrossAmount>130</GrossAmount>
<VATAmount>0</VATAmount>
<NetAmount>130</NetAmount>
<ProductCode>my Product</ProductCode>
<ItemDescriptionfield>Sudonim Examinational processing - FINAL PAYMENT</ItemDescriptionfield>
<Receiptnumber>1023</Receiptnumber>
<Authorisationcode>0101010</Authorisationcode>
<RecordedTransactionNumber>TRNDMUXXX</RecordedTransactionNumber>
<Transactiondate>29/01/2009 12:07:01.001</Transactiondate>
<TransactionTime>0.135416667</TransactionTime>
<Emailaddress>Sudo@Nim.com</Emailaddress>
<YearofStudy>YEAR</YearofStudy>
<Vatcode>12</Vatcode>
<CompanyID>1</CompanyID>
<questions><question>pnumber</question><answer>12345678X</answer>
<question>marked input</question><answer>oxford</answer>
<question>valueb</question><answer>subquark</answer>
<question>manikin</question><answer>tuesday</answer></questions>
</record>
insert into table records (QLXID, QLSID, GeneralLedgercode, .., Vatcode, CompanyID)
select fields from xml.....
 
the "questions" nested part can be ignored at this point, as it will be in a seperate XML string to be appended to the Questions table, with the ID identity from the "records" table as its foreign Key.

Open in new window

Avatar of Zberteoc
Zberteoc
Flag of Canada image
What is the source for the XML data? Is it an external file or a table in the database?
Avatar of reb73
reb73
Flag of Ireland image
The following code demonstrates how to open the contents of an XML construct using OPENXML -

You can work at manipulating the output for using in your insert statement..
declare @idoc int
declare @doc varchar(4000)
set @doc ='
<ROOT>
    <record>
	<QLXID></QLXID>
	<QLSID>DMU</QLSID>
	<GeneralLedgercode>13.X.XXX.1.432</GeneralLedgercode>
	<Customerfirstname>Sudo</Customerfirstname>
	<Customersurname>Nim</Customersurname>
	<PaymentMethod>D</PaymentMethod>
	<CardHolderName>Sudonim</CardHolderName>
	<WPAYDOM_DDMM>WPAYDOM_2901</WPAYDOM_DDMM>
	<PaymentType>STORE</PaymentType>
	<GrossAmount>130</GrossAmount>
	<VATAmount>0</VATAmount>
	<NetAmount>130</NetAmount>
	<ProductCode>my Product</ProductCode>
	<ItemDescriptionfield>Sudonim Examinational processing - FINAL PAYMENT</ItemDescriptionfield>
	<Receiptnumber>1023</Receiptnumber>
	<Authorisationcode>0101010</Authorisationcode>
	<RecordedTransactionNumber>TRNDMUXXX</RecordedTransactionNumber>
	<Transactiondate>29/01/2009 12:07:01.001</Transactiondate>
	<TransactionTime>0.135416667</TransactionTime>
	<Emailaddress>Sudo@Nim.com</Emailaddress>
	<YearofStudy>YEAR</YearofStudy>
	<Vatcode>12</Vatcode>
	<CompanyID>1</CompanyID>
    </record>
</ROOT>
'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement using OPENXML rowset provider
SELECT    *
FROM       OPENXML (@idoc, '/ROOT/record',3)
 
EXEC sp_xml_removedocument @idoc

Open in new window

Avatar of Si Ball
Si Ball
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Zberteoc - the xml will be being passed as text from a web service to a stored procedure on the database server... the SP will process the parent and child records and append them to temporary tables.....audit them and append them to the main tables.....

a "sub" stored procedure will check for certain values in the GeneralLedgerCode field and perform other sub routines if found, making data extracts to send on to other systems.

Reb73.  The output i get is attached as code....its interesting.  

i added a temp table append to it so i could sort it by field...and start to look at how to draw out the data.  many thanks.


declare @idoc int
declare @doc varchar(4000)
set @doc ='
<ROOT>
    <record>
        <QLXID></QLXID>
        <QLSID>DMU</QLSID>
        <GeneralLedgercode>13.X.XXX.1.432</GeneralLedgercode>
        <Customerfirstname>Sudo</Customerfirstname>
        <Customersurname>Nim</Customersurname>
        <PaymentMethod>D</PaymentMethod>
        <CardHolderName>Sudonim</CardHolderName>
        <WPAYDOM_DDMM>WPAYDOM_2901</WPAYDOM_DDMM>
        <PaymentType>STORE</PaymentType>
        <GrossAmount>130</GrossAmount>
        <VATAmount>0</VATAmount>
        <NetAmount>130</NetAmount>
        <ProductCode>my Product</ProductCode>
        <ItemDescriptionfield>Sudonim Examinational processing - FINAL PAYMENT</ItemDescriptionfield>
        <Receiptnumber>1023</Receiptnumber>
        <Authorisationcode>0101010</Authorisationcode>
        <RecordedTransactionNumber>TRNDMUXXX</RecordedTransactionNumber>
        <Transactiondate>29/01/2009 12:07:01.001</Transactiondate>
        <TransactionTime>0.135416667</TransactionTime>
        <Emailaddress>Sudo@Nim.com</Emailaddress>
        <YearofStudy>YEAR</YearofStudy>
        <Vatcode>12</Vatcode>
        <CompanyID>1</CompanyID>
    </record>
</ROOT>
'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement using OPENXML rowset provider
SELECT    * into #record
FROM       OPENXML (@idoc, '/ROOT/record',3)
 
EXEC sp_xml_removedocument @idoc
 
select * from #record order by id, parentid
 
 
 
2	0	1	record	NULL	NULL	NULL	NULL	NULL
3	2	1	QLXID	NULL	NULL	NULL	NULL	NULL
4	2	1	QLSID	NULL	NULL	NULL	3	NULL
5	2	1	GeneralLedgercode	NULL	NULL	NULL	4	NULL
6	2	1	Customerfirstname	NULL	NULL	NULL	5	NULL
7	2	1	Customersurname	NULL	NULL	NULL	6	NULL
8	2	1	PaymentMethod	NULL	NULL	NULL	7	NULL
9	2	1	CardHolderName	NULL	NULL	NULL	8	NULL
10	2	1	WPAYDOM_DDMM	NULL	NULL	NULL	9	NULL
11	2	1	PaymentType	NULL	NULL	NULL	10	NULL
12	2	1	GrossAmount	NULL	NULL	NULL	11	NULL
13	2	1	VATAmount	NULL	NULL	NULL	12	NULL
14	2	1	NetAmount	NULL	NULL	NULL	13	NULL
15	2	1	ProductCode	NULL	NULL	NULL	14	NULL
16	2	1	ItemDescriptionfield	NULL	NULL	NULL	15	NULL
17	2	1	Receiptnumber	NULL	NULL	NULL	16	NULL
18	2	1	Authorisationcode	NULL	NULL	NULL	17	NULL
19	2	1	RecordedTransactionNumber	NULL	NULL	NULL	18	NULL
20	2	1	Transactiondate	NULL	NULL	NULL	19	NULL
21	2	1	TransactionTime	NULL	NULL	NULL	20	NULL
22	2	1	Emailaddress	NULL	NULL	NULL	21	NULL
23	2	1	YearofStudy	NULL	NULL	NULL	22	NULL
24	2	1	Vatcode	NULL	NULL	NULL	23	NULL
25	2	1	CompanyID	NULL	NULL	NULL	24	NULL
26	4	3	#text	NULL	NULL	NULL	NULL	DMU
27	5	3	#text	NULL	NULL	NULL	NULL	13.X.XXX.1.432
28	6	3	#text	NULL	NULL	NULL	NULL	Sudo
29	7	3	#text	NULL	NULL	NULL	NULL	Nim
30	8	3	#text	NULL	NULL	NULL	NULL	D
31	9	3	#text	NULL	NULL	NULL	NULL	Sudonim
32	10	3	#text	NULL	NULL	NULL	NULL	WPAYDOM_2901
33	11	3	#text	NULL	NULL	NULL	NULL	STORE
34	12	3	#text	NULL	NULL	NULL	NULL	130
35	13	3	#text	NULL	NULL	NULL	NULL	0
36	14	3	#text	NULL	NULL	NULL	NULL	130
37	15	3	#text	NULL	NULL	NULL	NULL	my Product
38	16	3	#text	NULL	NULL	NULL	NULL	Sudonim Examinational processing - FINAL PAYMENT
39	17	3	#text	NULL	NULL	NULL	NULL	1023
40	18	3	#text	NULL	NULL	NULL	NULL	0101010
41	19	3	#text	NULL	NULL	NULL	NULL	TRNDMUXXX
42	20	3	#text	NULL	NULL	NULL	NULL	29/01/2009 12:07:01.001
43	21	3	#text	NULL	NULL	NULL	NULL	0.135416667
44	22	3	#text	NULL	NULL	NULL	NULL	Sudo@Nim.com
45	23	3	#text	NULL	NULL	NULL	NULL	YEAR
46	24	3	#text	NULL	NULL	NULL	NULL	12
47	25	3	#text	NULL	NULL	NULL	NULL	1

Open in new window

Avatar of Si Ball
Si Ball
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

linked table to itself to get fields and their values, had to use left join for when Text is null

i can use a cursor to trasnpose the records into an insert query....into a temporary table, then append that data into the real table, and get back the id, so i can go onto the next part, appending the child records.



select r1.id, r1.localname as field, r2.text as value into #fldVal from #record r1 left join #record r2 on
r1.id = r2.parentid where r1.parentid = 2
 
drop table #import
CREATE TABLE #import (
  blank varchar(1) NULL
  )
insert into #import (blank) select ''
--alter table #import add mycolumn varchar(250)
declare @fld varchar(100),@val varchar(250),
	@sql varchar (1000), @updSQL varchar(1000)
 
declare curse insensitive cursor for 
Select cast(field as varchar(100)), cast(value as varchar(250))
from  #fldVal
/** drops the cursive function into the loop and executes **/
open curse
fetch next from curse into @fld, @val
while @@fetch_status = 0
begin
set @sql = ('alter table #import add ' + @fld + ' varchar(250)')
set @updsql = ('update #import set ' + @fld + ' = '''+ @val + '''')
--print ''
--print (@updsql)
exec (@sql)
exec (@updsql)
    fetch next from curse into @fld, @val
/** closes and deallocates the cursor end of script **/
end 
close curse
deallocate curse

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Si Ball
Si Ball
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

superb solution