February 15, 2011 at 4:17 pm
Thank you Mister Magoo! I will probably forgo extracting the TransactionDate, only because there is a GETDATE default on that column, and I want that timestamp for when the transactions go into the table.
The only other thing I was wondering about is if I need to do this for every record in the XML file, or is this just needed at the bottom of my XML listing? I'll have to come up with an INSERT INTO method for it after it returns the result, but that shouldn't be too difficult.
Many thanks again!
February 15, 2011 at 4:22 pm
It will process the whole xml file.
To use it in an insert , it behaves just like any select statement.
Please try this version instead (went back to the cross apply method after load testing showed my way to be very poor)
SELECT
tn.value('@ID', 'tinyint') AS TransactionID, -- taken from the <Transaction> node
an.value('@ID', 'varchar(10)') AS AccountID, -- taken from the <Account> node
tn.value('@Type', 'varchar(100)') AS TransactionType, -- taken from the <Transaction> node
cn.value('@CustomerID', 'int') AS CustomerID, -- taken from the <Customer> node
tn.value('@Date', 'varchar(10)') AS TransactionDate, -- taken from the <Transaction> node
tn.value ('@Amount', 'money') AS TransactionAmount, -- taken from the <Transaction> node
tn.value ('@NewBalance', 'money') AS NewBalance -- taken from the <Transaction> node
FROM
@XML.nodes ('/ROOT/Customer') as cust(cn)
cross apply
cn.nodes('Accounts/*') as acc(an)
cross apply
an.nodes('Transactions/Transaction') AS tra(tn)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 15, 2011 at 4:26 pm
MM
using your very fine example I'd suggest using CROSS APPLY instead of "climbing up" the nodes level.
Just compare execution plan and query statistics... (on a larger set of data, of course)
SELECT
trans.value('@ID', 'tinyint') AS TransactionID, -- taken from the current node
acc.value('@ID', 'varchar(10)') AS AccountID, -- taken from the parent/parent node <Account>
trans.value('@Type', 'varchar(100)') AS TransactionType, -- taken from the current node
cust.value('@CustomerID', 'int') AS CustomerID, -- taken from the parent/parent/parent/parent node <Customer>
trans.value('@Date', 'varchar(10)') AS TransactionDate, -- taken from the current node
trans.value ('@Amount', 'money') AS TransactionAmount, -- taken from the current node
trans.value ('@NewBalance', 'money') AS NewBalance -- taken from the current node
FROM
@XML.nodes ('/ROOT/Customer') AS rt(cust)
CROSS APPLY rt.cust.nodes ('Accounts/*') AS cust(acc)
CROSS APPLY cust.acc.nodes ('Transactions/Transaction') AS acc(trans)
Edit: Well, that makes my post pretty much useless... Echo not intended though. Sorry!
February 15, 2011 at 4:51 pm
Hey Lutz and Magoo?
I'll admit my XQuery is very weak. One of the things that's always confused me is this part:
FROM
@XML.nodes ('/ROOT/Customer') as cust(cn)
cross apply
cn.nodes('Accounts/*') as acc(an)
cross apply
an.nodes('Transactions/Transaction') AS tra(tn)
The cn/an/tn parts I understand, but why is it necessary to use cust(cn), or x(cn), x(an), x(tn)? What's the purpose of the x part?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 15, 2011 at 4:51 pm
hey no problem Lutz ... it happens
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 15, 2011 at 4:59 pm
Hey gang - thank you again!
Just one last thing...
My DECLARE and SET up at the top of the XML data should be. . .???
February 15, 2011 at 5:09 pm
Craig Farrell (2/15/2011)
Hey Lutz and Magoo?I'll admit my XQuery is very weak. One of the things that's always confused me is this part:
FROM
@XML.nodes ('/ROOT/Customer') as cust(cn)
cross apply
cn.nodes('Accounts/*') as acc(an)
cross apply
an.nodes('Transactions/Transaction') AS tra(tn)
The cn/an/tn parts I understand, but why is it necessary to use cust(cn), or x(cn), x(an), x(tn)? What's the purpose of the x part?
Let's see if I can beat MM this time (by still being correct...):
You can basically see the nodes reference as a "loose table definition" with a single xml column.
Therefore, it's sometimes referenced as T(c) which is Table(column).
The one column itself holds another set of XML data that again can be referenced in a similar way.
So, the CROSS APPLY usually would be written as
CROSS APPLY Table.column.nodes('') AS YetAnotherTable(YetAnotherXmlColumn)
CROSS APPLY YetAnotherTable.YetAnotherXmlColumn.nodes('') AS Tx(cy)
When selecting data out of such an XML column, usually only the column name is referenced, since it needs to be unique with the XML "columns".
But to stay within the qualified naming convention, it better should be written as
SELECT
acc.trans.value('@ID', 'tinyint') AS TransactionID, -- taken from the current node
cust.acc.value('@ID', 'varchar(10)') AS AccountID, -- taken from the parent/parent node <Account>
acc.trans.value('@Type', 'varchar(100)') AS TransactionType, -- taken from the current node
rt.cust.value('@CustomerID', 'int') AS CustomerID, -- taken from the parent/parent/parent/parent node <Customer>
acc.trans.value('@Date', 'varchar(10)') AS TransactionDate, -- taken from the current node
acc.trans.value ('@Amount', 'money') AS TransactionAmount, -- taken from the current node
acc.trans.value ('@NewBalance', 'money') AS NewBalance -- taken from the current node
FROM
@XML.nodes ('/ROOT/Customer') AS rt(cust)
CROSS APPLY rt.cust.nodes ('Accounts/*') AS cust(acc)
CROSS APPLY cust.acc.nodes ('Transactions/Transaction') AS acc(trans)
February 15, 2011 at 5:11 pm
Rich Yarger (2/15/2011)
Hey gang - thank you again!Just one last thing...
My DECLARE and SET up at the top of the XML data should be. . .???
... like the example Mister Magoo provided in his very first reply. 😉
February 15, 2011 at 5:12 pm
LutzM (2/15/2011)
Rich Yarger (2/15/2011)
Hey gang - thank you again!Just one last thing...
My DECLARE and SET up at the top of the XML data should be. . .???
... like the example Mister Magoo provided in his very first reply. 😉
And that is why I am a newbie!
Thanks again Lutz!
😉
February 15, 2011 at 5:15 pm
Blaaaaaaaaaaaaaaaaaaaaaaaaaaah. . .Columns - not data. 🙁
DECLARE @XML xml;
SET @XML ='
<Root>
<Customer ID="1" FirstName="John" LastName="Doe" Street="123, Fake Street" City="Anytown" State="NY" Zip="11280" HomePhone="555-123-4567" WorkPhone="555-456-7890" MobilePhone="555-111-2345" EMail="jDoe@someweb.com">
<Accounts>
<Saving ID="100000" Balance="548.25" Active="1">
<Transactions>
<Transaction ID="1" Type="Initial Deposit" Date="4/01/2009" Amount="500.00" NewBalance="500.00" />
<Transaction ID="5" Type="Deposit" Date="4/01/2009" Amount="50.00" NewBalance="550.00" />
<Transaction ID="9" Type="Withdraw" Date="4/01/2009" Amount="1.75" NewBalance="548.25" />
</Transactions>
</Saving>
<Checking ID="100001" Balance="650.00" Active="1" OverdraftAccount="100000">
<Transactions>
<Transaction ID="4" Type="Initial Deposit" Date="4/01/2009" Amount="450.00" NewBalance="450.00" />
<Transaction ID="6" Type="Deposit" Date="4/01/2009" Amount="50.00" NewBalance="500.00" />
<Transaction ID="44" Type="Deposit" Date="4/01/2009" Amount="150.00" NewBalance="650.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="2" FirstName="Michael" LastName="Jones" Street="123, Really Fake Street" City="Mytown" State="NY" Zip="11214" HomePhone="555-123-4567" WorkPhone="555-456-7890" MobilePhone="555-111-2345" EMail="MJones@someweb.com">
<Accounts>
<Saving ID="100002" Balance="300.00" Active="1">
<Transactions>
<Transaction ID="8" Type="Initial Deposit" Date="4/01/2009" Amount="300.00" NewBalance="300.00" />
<Transaction ID="41" Type="Deposit" Date="4/01/2009" Amount="50.00" NewBalance="350.00" />
<Transaction ID="49" Type="Withdraw" Date="4/01/2009" Amount="50.00" NewBalance="300.00" />
</Transactions>
</Saving>
<Checking ID="100003" Balance="700.00" Active="1" OverdraftAccount="100002">
<Transactions>
<Transaction ID="2" Type="Initial Deposit" Date="4/01/2009" Amount="450.00" NewBalance="450.00" />
<Transaction ID="11" Type="Deposit" Date="4/01/2009" Amount="50.00" NewBalance="500.00" />
<Transaction ID="12" Type="Deposit" Date="4/01/2009" Amount="200.00" NewBalance="700.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="3" FirstName="Erica" LastName="Jones" Street="123, Really Fake Street" City="Mytown" State="NY" Zip="11218" HomePhone="555-564-4557" WorkPhone="567-456-7643" MobilePhone="563-134-2345" EMail="EJones@someweb.com">
<Accounts>
<Saving ID="100004" Balance="1000.00" Active="1">
<Transactions>
<Transaction ID="3" Type="Initial Deposit" Date="4/01/2009" Amount="900.00" NewBalance="900.00" />
<Transaction ID="14" Type="Deposit" Date="4/01/2009" Amount="50.00" NewBalance="950.00" />
<Transaction ID="15" Type="Withdraw" Date="4/01/2009" Amount="50.00" NewBalance="900.00" />
</Transactions>
</Saving>
<Checking ID="100005" Balance="850.00" Active="1" OverdraftAccount="100004">
<Transactions>
<Transaction ID="13" Type="Initial Deposit" Date="4/01/2009" Amount="1,400.00" NewBalance="1,400.00" />
<Transaction ID="17" Type="Deposit" Date="4/01/2009" Amount="50.00" NewBalance="1450.00" />
<Transaction ID="18" Type="Withdraw" Date="4/01/2009" Amount="600.00" NewBalance="850.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="4" FirstName="Darshan" LastName="Mehta" Street="1235, Trainer Road" City="Mytown" State="NY" Zip="11219" HomePhone="555-123-4567" WorkPhone="555-456-7890" MobilePhone="555-111-2345" EMail="DMehta@setfocus.com">
<Accounts>
<Saving ID="100006" Balance="35,000.00" Active="1">
<Transactions>
<Transaction ID="16" Type="Initial Deposit" Date="4/01/2009" Amount="22,000.00" NewBalance="22,000.00" />
<Transaction ID="20" Type="Deposit" Date="4/01/2009" Amount="10,000.00" NewBalance="32,000.00" />
<Transaction ID="21" Type="Withdraw" Date="4/01/2009" Amount="3,000.00" NewBalance="35,000.00" />
</Transactions>
</Saving>
<Checking ID="100007" Balance="1,000" Active="1" OverdraftAccount="100006">
<Transactions>
<Transaction ID="19" Type="Initial Deposit" Date="4/01/2009" Amount="600.00" NewBalance="600.00" />
<Transaction ID="23" Type="Deposit" Date="4/01/2009" Amount="500.00" NewBalance="1100.00" />
<Transaction ID="24" Type="Withdraw" Date="4/01/2009" Amount="60.00" NewBalance="1040.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="5" FirstName="Donald" LastName="Trump" Street="635, 42nd Ave." City="Mytown" State="NY" Zip="11224" HomePhone="555-123-9993" WorkPhone="555-676-7890" MobilePhone="555-161-2345" EMail="DTrump@someweb.com">
<Accounts>
<Saving ID="100008" Balance="21,000.00" Active="1">
<Transactions>
<Transaction ID="10" Type="Initial Deposit" Date="4/01/2009" Amount="7,000.00" NewBalance="7,000.00" />
<Transaction ID="26" Type="Deposit" Date="4/01/2009" Amount="10,000.00" NewBalance="17,000.00" />
<Transaction ID="27" Type="Withdraw" Date="4/01/2009" Amount="3,000.00" NewBalance="14,000.00" />
<Transaction ID="28" Type="Deposit" Date="4/01/2009" Amount="10,000.00" NewBalance="24,000.00" />
<Transaction ID="29" Type="Withdraw" Date="4/01/2009" Amount="3,000.00" NewBalance="21,000.00" />
</Transactions>
</Saving>
<Checking ID="100009" Balance="99,960.00" Active="1" OverdraftAccount="100008">
<Transactions>
<Transaction ID="22" Type="Initial Deposit" Date="4/01/2009" Amount="100,000.00" NewBalance="100,000.00" />
<Transaction ID="31" Type="Deposit" Date="4/01/2009" Amount="500.00" NewBalance="100,500.00" />
<Transaction ID="32" Type="Withdraw" Date="4/01/2009" Amount="80.00" NewBalance="100,420.00" />
<Transaction ID="99" Type="Withdraw" Date="4/01/2009" Amount="80.00" NewBalance="100,360.00" />
<Transaction ID="100" Type="Withdraw" Date="4/02/2009" Amount="80.00" NewBalance="100,280.00" />
<Transaction ID="110" Type="Withdraw" Date="4/02/2009" Amount="80.00" NewBalance="100,200.00" />
<Transaction ID="121" Type="Withdraw" Date="4/02/2009" Amount="80.00" NewBalance="100,120.00" />
<Transaction ID="132" Type="Withdraw" Date="4/02/2009" Amount="80.00" NewBalance="100,040.00" />
<Transaction ID="143" Type="Withdraw" Date="4/02/2009" Amount="80.00" NewBalance="99,960.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="6" FirstName="Slim" LastName="Shady" Street="1212, 42nd Ave." City="Detroit" State="MI" Zip="11285" HomePhone="602-128-4567" WorkPhone="602-445-8890" MobilePhone="602-111-2345" EMail="Slim@someweb.com">
<Accounts>
<Saving ID="100010" Balance="117,000.00" Active="1">
<Transactions>
<Transaction ID="30" Type="Initial Deposit" Date="4/01/2009" Amount="7,000.00" NewBalance="7,000.00" />
<Transaction ID="98" Type="Deposit" Date="4/01/2009" Amount="200,000.00" NewBalance="207,000.00" />
<Transaction ID="111" Type="Deposit" Date="4/02/2009" Amount="200,000.00" NewBalance="407,000.00" />
<Transaction ID="133" Type="Deposit" Date="4/02/2009" Amount="10,000.00" NewBalance="417,000.00" />
<Transaction ID="142" Type="Withdraw" Date="4/02/2009" Amount="300,000.00" NewBalance="117,000.00" />
</Transactions>
</Saving>
<Checking ID="100011" Balance="89,600.00" Active="1" OverdraftAccount="100008">
<Transactions>
<Transaction ID="33" Type="Initial Deposit" Date="4/01/2009" Amount="100,000.00" NewBalance="100,000.00" />
<Transaction ID="97" Type="Withdraw" Date="4/01/2009" Amount="200.00" NewBalance="90,800.00" />
<Transaction ID="113" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="90,600.00" />
<Transaction ID="124" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="90,400.00" />
<Transaction ID="138" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="90,200.00" />
<Transaction ID="140" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="90,000.00" />
<Transaction ID="152" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="89,800.00" />
<Transaction ID="161" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="89,600.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="7" FirstName="Daddy" LastName="Warshucks" Street="205, 42nd Ave." City="Notatown" State="NY" Zip="11295" HomePhone="212-123-4567" WorkPhone="212-456-5540" MobilePhone="212-111-4458" EMail="DWshuck@someweb.com">
<Accounts>
<Checking ID="100012" Balance="5,000.00" Active="1" OverdraftAccount="">
<Transactions>
<Transaction ID="25" Type="Initial Deposit" Date="4/01/2009" Amount="6,000.00" NewBalance="6,000.00" />
<Transaction ID="53" Type="Withdraw" Date="4/01/2009" Amount="200.00" NewBalance="5,800.00" />
<Transaction ID="57" Type="Withdraw" Date="4/01/2009" Amount="200.00" NewBalance="5,600.00" />
<Transaction ID="125" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="5,400.00" />
<Transaction ID="148" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="5,200.00" />
<Transaction ID="162" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="5,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="8" FirstName="Eddie" LastName="Vedder" Street="1432, 1st St." City="Seattle" State="WA" Zip="12345" HomePhone="789-123-4567" WorkPhone="789-456-1576" MobilePhone="789-111-8794" EMail="EVedder@someweb.com">
<Accounts>
<Saving ID="100013" Balance="14,000.00" Active="1">
<Transactions>
<Transaction ID="54" Type="Initial Deposit" Date="4/01/2009" Amount="3,000.00" NewBalance="3,000.00" />
<Transaction ID="59" Type="Deposit" Date="4/01/2009" Amount="2,000.00" NewBalance="5,000.00" />
<Transaction ID="60" Type="Deposit" Date="4/01/2009" Amount="2,000.00" NewBalance="7,000.00" />
<Transaction ID="71" Type="Deposit" Date="4/01/2009" Amount="2,000.00" NewBalance="9,000.00" />
<Transaction ID="73" Type="Withdraw" Date="4/01/2009" Amount="3,000.00" NewBalance="6,000.00" />
<Transaction ID="77" Type="Deposit" Date="4/01/2009" Amount="7,000.00" NewBalance="13,000.00" />
<Transaction ID="108" Type="Deposit" Date="4/02/2009" Amount="2,000.00" NewBalance="15,000.00" />
<Transaction ID="119" Type="Deposit" Date="4/02/2009" Amount="2,000.00" NewBalance="15,000.00" />
<Transaction ID="126" Type="Deposit" Date="4/02/2009" Amount="2,000.00" NewBalance="17,000.00" />
<Transaction ID="163" Type="Withdraw" Date="4/02/2009" Amount="3,000.00" NewBalance="14,000.00" />
</Transactions>
</Saving>
<Checking ID="100014" Balance="3,500.00" Active="1" OverdraftAccount="100013">
<Transactions>
<Transaction ID="58" Type="Initial Deposit" Date="4/01/2009" Amount="4,000.00" NewBalance="100,000.00" />
<Transaction ID="78" Type="Withdraw" Date="4/01/2009" Amount="100.00" NewBalance="3,900.00" />
<Transaction ID="88" Type="Withdraw" Date="4/01/2009" Amount="100.00" NewBalance="3,800.00" />
<Transaction ID="171" Type="Withdraw" Date="4/02/2009" Amount="100.00" NewBalance="3,700.00" />
<Transaction ID="194" Type="Withdraw" Date="4/02/2009" Amount="100.00" NewBalance="3,600.00" />
<Transaction ID="202" Type="Withdraw" Date="4/02/2009" Amount="100.00" NewBalance="3,500.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="9" FirstName="Wyatt" LastName="Earp" Street="780 Deadwood" City="Kansas City" State="MO" Zip="33234" HomePhone="432-123-4567" WorkPhone="432-456-7894" MobilePhone="432-111-3214" EMail="WEarp@someweb.com">
<Accounts>
<Saving ID="100015" Balance="117,000.00" Active="1">
<Transactions>
<Transaction ID="74" Type="Initial Deposit" Date="4/01/2009" Amount="7,000.00" NewBalance="7,000.00" />
<Transaction ID="170" Type="Deposit" Date="4/02/2009" Amount="200,000.00" NewBalance="207,000.00" />
<Transaction ID="179" Type="Deposit" Date="4/02/2009" Amount="200,000.00" NewBalance="407,000.00" />
<Transaction ID="198" Type="Deposit" Date="4/02/2009" Amount="10,000.00" NewBalance="417,000.00" />
<Transaction ID="203" Type="Withdraw" Date="4/02/2009" Amount="300,000.00" NewBalance="117,000.00" />
</Transactions>
</Saving>
<Checking ID="100016" Balance="89,600.00" Active="1" OverdraftAccount="100015">
<Transactions>
<Transaction ID="79" Type="Initial Deposit" Date="4/01/2009" Amount="100,000.00" NewBalance="100,000.00" />
<Transaction ID="89" Type="Withdraw" Date="4/01/2009" Amount="200.00" NewBalance="90,800.00" />
<Transaction ID="144" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="90,600.00" />
<Transaction ID="155" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="90,400.00" />
<Transaction ID="166" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="90,200.00" />
<Transaction ID="195" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="90,000.00" />
<Transaction ID="197" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="89,800.00" />
<Transaction ID="201" Type="Withdraw" Date="4/02/2009" Amount="200.00" NewBalance="89,600.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="10" FirstName="Amy" LastName="Laffer" Street="129, Bayshore Ave." City="Mytown" State="NY" Zip="14023" HomePhone="607-564-4657" WorkPhone="607-456-7643" MobilePhone="607-134-2345" EMail="ALaffer@someweb.com">
<Accounts>
<Checking ID="100017" Balance="1000.00" Active="1" OverdraftAccount="">
<Transactions>
<Transaction ID="301" Type="Initial Deposit" Date="4/02/2009" Amount="1,000.00" NewBalance="1,000.00" />
<Transaction ID="302" Type="Deposit" Date="4/02/2009" Amount="500.00" NewBalance="1500.00" />
<Transaction ID="303" Type="Withdraw" Date="4/02/2009" Amount="500.00" NewBalance="1000.00" />
</Transactions>
</Checking>
<Checking ID="100018" Balance="850.00" Active="1" OverdraftAccount="">
<Transactions>
<Transaction ID="304" Type="Initial Deposit" Date="4/03/2009" Amount="1,400.00" NewBalance="1,400.00" />
<Transaction ID="305" Type="Deposit" Date="4/03/2009" Amount="50.00" NewBalance="1450.00" />
<Transaction ID="306" Type="Withdraw" Date="4/03/2009" Amount="600.00" NewBalance="850.00" />
</Transactions>
</Checking>
<Checking ID="100019" Balance="900.00" Active="1" OverdraftAccount="">
<Transactions>
<Transaction ID="307" Type="Initial Deposit" Date="4/03/2009" Amount="900.00" NewBalance="900.00" />
<Transaction ID="308" Type="Deposit" Date="4/03/2009" Amount="500.00" NewBalance="1400.00" />
<Transaction ID="309" Type="Withdraw" Date="4/03/2009" Amount="500.00" NewBalance="900.00" />
</Transactions>
</Checking>
<Checking ID="100020" Balance="600.00" Active="1" OverdraftAccount="">
<Transactions>
<Transaction ID="310" Type="Initial Deposit" Date="4/03/2009" Amount="1,100.00" NewBalance="1,100.00" />
<Transaction ID="311" Type="Deposit" Date="4/03/2009" Amount="50.00" NewBalance="1,150.00" />
<Transaction ID="312" Type="Withdraw" Date="4/03/2009" Amount="400.00" NewBalance="750.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="11" FirstName="Ivana" LastName="Baskalisk" Street="6193, 42nd Ave." City="Mytown" State="NY" Zip="11345" HomePhone="212-129-9993" WorkPhone="212-676-7890" MobilePhone="212-161-2545" EMail="DTrump@someweb.com">
<Accounts>
<Saving ID="100021" Balance="2,000.00" Active="1">
<Transactions>
<Transaction ID="313" Type="Initial Deposit" Date="4/03/2009" Amount="9,000.00" NewBalance="9,000.00" />
<Transaction ID="314" Type="Deposit" Date="4/03/2009" Amount="10,000.00" NewBalance="19,000.00" />
<Transaction ID="315" Type="Withdraw" Date="4/03/2009" Amount="3000.00" NewBalance="16,000.00" />
<Transaction ID="316" Type="Deposit" Date="4/03/2009" Amount="10,000.00" NewBalance="26,000.00" />
<Transaction ID="317" Type="Withdraw" Date="4/03/2009" Amount="24,000.00" NewBalance="2,000.00" />
</Transactions>
</Saving>
<Saving ID="100022" Balance="217,000.00" Active="1">
<Transactions>
<Transaction ID="318" Type="Initial Deposit" Date="4/03/2009" Amount="7,000.00" NewBalance="7,000.00" />
<Transaction ID="319" Type="Deposit" Date="4/03/2009" Amount="200,000.00" NewBalance="207,000.00" />
<Transaction ID="320" Type="Deposit" Date="4/03/2009" Amount="200,000.00" NewBalance="407,000.00" />
<Transaction ID="321" Type="Deposit" Date="4/03/2009" Amount="10,000.00" NewBalance="417,000.00" />
<Transaction ID="322" Type="Withdraw" Date="4/03/2009" Amount="200,000.00" NewBalance="217,000.00" />
</Transactions>
</Saving>
<Checking ID="100023" Balance="30,000.00" Active="1" OverdraftAccount="100022">
<Transactions>
<Transaction ID="323" Type="Initial Deposit" Date="4/03/2009" Amount="100,000.00" NewBalance="100,000.00" />
<Transaction ID="324" Type="Deposit" Date="4/03/2009" Amount="5,000.00" NewBalance="105,000.00" />
<Transaction ID="325" Type="Withdraw" Date="4/03/2009" Amount="500.00" NewBalance="104,500.00" />
<Transaction ID="326" Type="Withdraw" Date="4/03/2009" Amount="500.00" NewBalance="104,000.00" />
<Transaction ID="327" Type="Withdraw" Date="4/03/2009" Amount="5,000.00" NewBalance="99,000.00" />
<Transaction ID="328" Type="Withdraw" Date="4/03/2009" Amount="500.00" NewBalance="98,500.00" />
<Transaction ID="329" Type="Withdraw" Date="4/03/2009" Amount="50,000.00" NewBalance="48,500.00" />
<Transaction ID="330" Type="Withdraw" Date="4/03/2009" Amount="17,500.00" NewBalance="31,000.00" />
<Transaction ID="331" Type="Withdraw" Date="4/03/2009" Amount="1,000.00" NewBalance="30,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="12" FirstName="Teddy" LastName="Rupkin" Street="1234 Elizabeth Ave." City="Pasadena" State="CA" Zip="91104" HomePhone="626-791-1222" WorkPhone="626-791-3453" MobilePhone="626-234-1242" EMail="TRup@somewhere.net">
<Accounts>
<Saving ID="100024" Balance="5,000.00" Active="1">
<Transactions>
<Transaction ID="401" Type="Initial Deposit" Date="4/03/2009" Amount="1,000.00" NewBalance="1,000.00" />
<Transaction ID="501" Type="Deposit" Date="4/03/2009" Amount="13,000.00" NewBalance="14,000.00" />
<Transaction ID="601" Type="Deposit" Date="4/03/2009" Amount="1,000.00" NewBalance="15,000.00" />
<Transaction ID="701" Type="Deposit" Date="4/03/2009" Amount="10,000.00" NewBalance="25,000.00" />
<Transaction ID="801" Type="Withdraw" Date="4/03/2009" Amount="20,000.00" NewBalance="5,000.00" />
</Transactions>
</Saving>
<Checking ID="100025" Balance="14,000.00" Active="1" OverdraftAccount="100024">
<Transactions>
<Transaction ID="901" Type="Initial Deposit" Date="4/03/2009" Amount="5,000.00" NewBalance="5,000.00" />
<Transaction ID="1001" Type="Deposit" Date="4/04/2009" Amount="15,000.00" NewBalance="20,000.00" />
<Transaction ID="1101" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="19,500.00" />
<Transaction ID="1201" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="19,000.00" />
<Transaction ID="1301" Type="Withdraw" Date="4/04/2009" Amount="5,000.00" NewBalance="14,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="13" FirstName="Jim" LastName="Igawa" Street="578 1st Ave." City="San Francisco" State="CA" Zip="94107" HomePhone="415-895-9865" WorkPhone="415-789-9865" MobilePhone="415-898-6655" EMail="JIgawa@somewhere.net">
<Accounts>
<Saving ID="100026" Balance="33,000.00" Active="1">
<Transactions>
<Transaction ID="1801" Type="Initial Deposit" Date="4/04/2009" Amount="9,000.00" NewBalance="9,000.00" />
<Transaction ID="1901" Type="Deposit" Date="4/04/2009" Amount="6,000.00" NewBalance="17,000.00" />
<Transaction ID="2001" Type="Deposit" Date="4/04/2009" Amount="8,000.00" NewBalance="25,000.00" />
<Transaction ID="2101" Type="Deposit" Date="4/04/2009" Amount="10,000.00" NewBalance="35,000.00" />
<Transaction ID="2201" Type="Withdraw" Date="4/04/2009" Amount="2,000.00" NewBalance="33,000.00" />
</Transactions>
</Saving>
<Checking ID="100027" Balance="14,500.00" Active="1" OverdraftAccount="100026">
<Transactions>
<Transaction ID="402" Type="Initial Deposit" Date="4/03/2009" Amount="10,000.00" NewBalance="10,000.00" />
<Transaction ID="502" Type="Deposit" Date="4/03/2009" Amount="5,000.00" NewBalance="15,000.00" />
<Transaction ID="602" Type="Withdraw" Date="4/03/2009" Amount="500.00" NewBalance="14,500.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="14" FirstName="Elana" LastName="Chileo" Street="876 Jones St." City="San Francisco" State="CA" Zip="94132" HomePhone="415-879-8956" WorkPhone="415-895-9865" MobilePhone="415-123-4565" EMail="EChileo@somewhere.net">
<Accounts>
<Saving ID="100028" Balance="610,500.00" Active="1">
<Transactions>
<Transaction ID="1302" Type="Initial Deposit" Date="4/04/2009" Amount="500.00" NewBalance="7,000.00" />
<Transaction ID="1402" Type="Deposit" Date="4/04/2009" Amount="400,000.00" NewBalance="407,000.00" />
<Transaction ID="1502" Type="Deposit" Date="4/04/2009" Amount="400,000.00" NewBalance="807,000.00" />
<Transaction ID="1602" Type="Deposit" Date="4/04/2009" Amount="10,000.00" NewBalance="810,500.00" />
<Transaction ID="1702" Type="Withdraw" Date="4/04/2009" Amount="200,000.00" NewBalance="610,500.00" />
</Transactions>
</Saving>
<Checking ID="100029" Balance="105,000.00" Active="1" OverdraftAccount="100028">
<Transactions>
<Transaction ID="403" Type="Initial Deposit" Date="4/03/2009" Amount="100,000.00" NewBalance="100,000.00" />
<Transaction ID="503" Type="Deposit" Date="4/03/2009" Amount="5,000.00" NewBalance="105,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="15" FirstName="Rick" LastName="James" Street="9834B Taylor St." City="San Francisco" State="CA" Zip="94134" HomePhone="415-735-6423" WorkPhone="415-784-5865" MobilePhone="415-789-9856" EMail="RJames@somewhere.net">
<Accounts>
<Saving ID="100030" Balance="23,300.00" Active="1">
<Transactions>
<Transaction ID="1303" Type="Initial Deposit" Date="4/04/2009" Amount="4,000.00" NewBalance="4,000.00" />
<Transaction ID="1403" Type="Deposit" Date="4/04/2009" Amount="20,000.00" NewBalance="24,000.00" />
<Transaction ID="1503" Type="Deposit" Date="4/04/2009" Amount="300.00" NewBalance="24,300.00" />
<Transaction ID="1603" Type="Deposit" Date="4/04/2009" Amount="10,000.00" NewBalance="34,300.00" />
<Transaction ID="1703" Type="Withdraw" Date="4/04/2009" Amount="11,000.00" NewBalance="23,300.00" />
</Transactions>
</Saving>
<Checking ID="100031" Balance="30,000.00" Active="1" OverdraftAccount="100030">
<Transactions>
<Transaction ID="1803" Type="Initial Deposit" Date="4/04/2009" Amount="8,000.00" NewBalance="100,000.00" />
<Transaction ID="1903" Type="Deposit" Date="4/04/2009" Amount="5,000.00" NewBalance="105,000.00" />
<Transaction ID="2003" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="104,500.00" />
<Transaction ID="2103" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="104,000.00" />
<Transaction ID="2203" Type="Withdraw" Date="4/04/2009" Amount="5,000.00" NewBalance="99,000.00" />
<Transaction ID="2303" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="98,500.00" />
<Transaction ID="2403" Type="Withdraw" Date="4/04/2009" Amount="50,000.00" NewBalance="48,500.00" />
<Transaction ID="2503" Type="Withdraw" Date="4/04/2009" Amount="17,500.00" NewBalance="31,000.00" />
<Transaction ID="2603" Type="Withdraw" Date="4/04/2009" Amount="1,000.00" NewBalance="30,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="16" FirstName="James" LastName="Rojas" Street="43 Eddy Ave." City="New York City" State="NY" Zip="11281" HomePhone="212-875-4256" WorkPhone="212-123-9856" MobilePhone="212-895-5645" EMail="JRojas@somewhere.net">
<Accounts>
<Saving ID="100032" Balance="217,000.00" Active="1">
<Transactions>
<Transaction ID="405" Type="Initial Deposit" Date="4/03/2009" Amount="7,000.00" NewBalance="7,000.00" />
<Transaction ID="505" Type="Deposit" Date="4/03/2009" Amount="200,000.00" NewBalance="207,000.00" />
<Transaction ID="508" Type="Deposit" Date="4/03/2009" Amount="200,000.00" NewBalance="407,000.00" />
<Transaction ID="605" Type="Deposit" Date="4/03/2009" Amount="10,000.00" NewBalance="417,000.00" />
<Transaction ID="705" Type="Withdraw" Date="4/03/2009" Amount="200,000.00" NewBalance="217,000.00" />
</Transactions>
</Saving>
<Checking ID="100033" Balance="7,000.00" Active="1" OverdraftAccount="100032">
<Transactions>
<Transaction ID="805" Type="Initial Deposit" Date="4/03/2009" Amount="3,000.00" NewBalance="3,000.00" />
<Transaction ID="905" Type="Deposit" Date="4/03/2009" Amount="5,000.00" NewBalance="8,000.00" />
<Transaction ID="1005" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="7,500.00" />
<Transaction ID="1105" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="7,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="17" FirstName="Robert" LastName="Frost" Street="678 Major Expressway" City="Boston" State="MA" Zip="11458" HomePhone="617-865-8954" WorkPhone="617-865-8956" MobilePhone="617-485-8564" EMail="RFrost34@somewhere.net">
<Accounts>
<Saving ID="100034" Balance="13,000.00" Active="1">
<Transactions>
<Transaction ID="407" Type="Initial Deposit" Date="4/03/2009" Amount="1,000.00" NewBalance="1,000.00" />
<Transaction ID="408" Type="Deposit" Date="4/03/2009" Amount="200,000.00" NewBalance="201,000.00" />
<Transaction ID="409" Type="Deposit" Date="4/03/2009" Amount="2,000.00" NewBalance="203,000.00" />
<Transaction ID="410" Type="Deposit" Date="4/03/2009" Amount="10,000.00" NewBalance="213,000.00" />
<Transaction ID="411" Type="Withdraw" Date="4/03/2009" Amount="200,000.00" NewBalance="13,000.00" />
</Transactions>
</Saving>
<Checking ID="100035" Balance="7,500.00" Active="1" OverdraftAccount="100034">
<Transactions>
<Transaction ID="512" Type="Initial Deposit" Date="4/03/2009" Amount="4,000.00" NewBalance="4,000.00" />
<Transaction ID="612" Type="Deposit" Date="4/03/2009" Amount="5,000.00" NewBalance="9,000.00" />
<Transaction ID="712" Type="Withdraw" Date="4/03/2009" Amount="1,500.00" NewBalance="7,500.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="18" FirstName="Greg" LastName="Gerger" Street="345 Wyatt Ave." City="Atlanta" State="GA" Zip="37125" HomePhone="402-865-9856" WorkPhone="402-865-8956" MobilePhone="402-865-9512" EMail="GGerner123412@somewhere.net">
<Accounts>
<Saving ID="100036" Balance="17,000.00" Active="1">
<Transactions>
<Transaction ID="1412" Type="Initial Deposit" Date="4/04/2009" Amount="12,000.00" NewBalance="12,000.00" />
<Transaction ID="1512" Type="Deposit" Date="4/04/2009" Amount="2,000.00" NewBalance="14,000.00" />
<Transaction ID="1612" Type="Deposit" Date="4/04/2009" Amount="3,000.00" NewBalance="17,000.00" />
</Transactions>
</Saving>
<Checking ID="100037" Balance="2,000.00" Active="1" OverdraftAccount="100036">
<Transactions>
<Transaction ID="1912" Type="Initial Deposit" Date="4/04/2009" Amount="4,000.00" NewBalance="4,000.00" />
<Transaction ID="2012" Type="Deposit" Date="4/04/2009" Amount="300.00" NewBalance="4,300.00" />
<Transaction ID="2112" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="3,800.00" />
<Transaction ID="2212" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="3,300.00" />
<Transaction ID="2712" Type="Withdraw" Date="4/04/2009" Amount="1,300.00" NewBalance="2,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="19" FirstName="Amy" LastName="Smart" Street="234 Bayshore" City="Houston" State="TX" Zip="38562" HomePhone="412-856-8052" WorkPhone="412-856-8945" MobilePhone="412-896-8956" EMail="ASmart34@somewhere.net">
<Accounts>
<Saving ID="100038" Balance="7,000.00" Active="1">
<Transactions>
<Transaction ID="420" Type="Initial Deposit" Date="4/03/2009" Amount="7,000.00" NewBalance="7,000.00" />
</Transactions>
</Saving>
<Checking ID="100039" Balance="69,000.00" Active="1" OverdraftAccount="100038">
<Transactions>
<Transaction ID="920" Type="Initial Deposit" Date="4/03/2009" Amount="70,000.00" NewBalance="70,000.00" />
<Transaction ID="1020" Type="Deposit" Date="4/04/2009" Amount="5,000.00" NewBalance="75,000.00" />
<Transaction ID="1120" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="74,500.00" />
<Transaction ID="1220" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="74,000.00" />
<Transaction ID="1320" Type="Withdraw" Date="4/04/2009" Amount="5,000.00" NewBalance="69,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="20" FirstName="Sully" LastName="Buddy" Street="943 Second Ave." City="Los Angeles" State="CA" Zip="90032" HomePhone="213-856-9875" WorkPhone="213-792-8654" MobilePhone="213-895-9865" EMail="Sally983@somewhere.net">
<Accounts>
<Saving ID="100040" Balance="17,000.00" Active="1">
<Transactions>
<Transaction ID="1820" Type="Initial Deposit" Date="4/04/2009" Amount="7,000.00" NewBalance="7,000.00" />
<Transaction ID="1920" Type="Deposit" Date="4/04/2009" Amount="200,000.00" NewBalance="207,000.00" />
<Transaction ID="2020" Type="Deposit" Date="4/04/2009" Amount="200,000.00" NewBalance="407,000.00" />
<Transaction ID="2120" Type="Deposit" Date="4/04/2009" Amount="10,000.00" NewBalance="417,000.00" />
<Transaction ID="2220" Type="Withdraw" Date="4/04/2009" Amount="400,000.00" NewBalance="17,000.00" />
</Transactions>
</Saving>
<Checking ID="100041" Balance="2,000.00" Active="1" OverdraftAccount="100040">
<Transactions>
<Transaction ID="2320" Type="Initial Deposit" Date="4/04/2009" Amount="2,000.00" NewBalance="2,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="21" FirstName="Buddy" LastName="Udder" Street="854 Lincoln Ave." City="San Francisco" State="CA" Zip="94127" HomePhone="415-456-8565" WorkPhone="415-999-8956" MobilePhone="415-856-8765" EMail="BUdder@somewhere.net">
<Accounts>
<Saving ID="100042" Balance="816,000.00" Active="1">
<Transactions>
<Transaction ID="3220" Type="Initial Deposit" Date="4/05/2009" Amount="6,000.00" NewBalance="6,000.00" />
<Transaction ID="3321" Type="Deposit" Date="4/05/2009" Amount="500,000.00" NewBalance="506,000.00" />
<Transaction ID="3420" Type="Deposit" Date="4/05/2009" Amount="500,000.00" NewBalance="1,006,000.00" />
<Transaction ID="3520" Type="Deposit" Date="4/05/2009" Amount="10,000.00" NewBalance="1,016,000.00" />
<Transaction ID="3620" Type="Withdraw" Date="4/05/2009" Amount="200,000.00" NewBalance="816,000.00" />
</Transactions>
</Saving>
<Checking ID="100043" Balance="3,000.00" Active="1" OverdraftAccount="100042">
<Transactions>
<Transaction ID="425" Type="Initial Deposit" Date="4/03/2009" Amount="100,000.00" NewBalance="100,000.00" />
<Transaction ID="925" Type="Withdraw" Date="4/03/2009" Amount="5,000.00" NewBalance="95,000.00" />
<Transaction ID="1025" Type="Withdraw" Date="4/04/2009" Amount="50,000.00" NewBalance="45,000.00" />
<Transaction ID="1125" Type="Withdraw" Date="4/04/2009" Amount="700.00" NewBalance="44,300.00" />
<Transaction ID="1225" Type="Withdraw" Date="4/04/2009" Amount="1,000.00" NewBalance="43,300.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="22" FirstName="Guy" LastName="Johnson" Street="874 Taylor Ave" City="San Francisco" State="CA " Zip="94105" HomePhone="415-875-7845" WorkPhone="415-894-8756" MobilePhone="415-986-8956" EMail="GJohnson@somewhere.net">
<Accounts>
<Saving ID="100044" Balance="44,000.00" Active="1">
<Transactions>
<Transaction ID="1325" Type="Initial Deposit" Date="4/04/2009" Amount="44,000.00" NewBalance="44,000.00" />
<Transaction ID="1425" Type="Deposit" Date="4/04/2009" Amount="2,000.00" NewBalance="46,000.00" />
<Transaction ID="1525" Type="Deposit" Date="4/04/2009" Amount="2,000.00" NewBalance="48,000.00" />
<Transaction ID="1625" Type="Deposit" Date="4/04/2009" Amount="2,000.00" NewBalance="50,000.00" />
<Transaction ID="1725" Type="Withdraw" Date="4/04/2009" Amount="6,000.00" NewBalance="44,000.00" />
</Transactions>
</Saving>
<Checking ID="100045" Balance="104,000.00" Active="1" OverdraftAccount="100044">
<Transactions>
<Transaction ID="1825" Type="Initial Deposit" Date="4/04/2009" Amount="3,000.00" NewBalance="3,000.00" />
<Transaction ID="1925" Type="Deposit" Date="4/04/2009" Amount="102,000.00" NewBalance="105,000.00" />
<Transaction ID="2025" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="104,500.00" />
<Transaction ID="2125" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="104,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="23" FirstName="Jim" LastName="Knock" Street="982 Gillette Ave." City="San Francisco" State="CA" Zip="94135" HomePhone="415-564-8965" WorkPhone="415-879-8976" MobilePhone="415-777-7865" EMail="JKnock@somewhere.net">
<Accounts>
<Saving ID="100046" Balance="22,000.00" Active="1">
<Transactions>
<Transaction ID="427" Type="Initial Deposit" Date="4/03/2009" Amount="1,000.00" NewBalance="1,000.00" />
<Transaction ID="527" Type="Deposit" Date="4/03/2009" Amount="9,000.00" NewBalance="10,000.00" />
<Transaction ID="627" Type="Deposit" Date="4/03/2009" Amount="2,000.00" NewBalance="12,000.00" />
<Transaction ID="727" Type="Deposit" Date="4/03/2009" Amount="10,000.00" NewBalance="22,000.00" />
</Transactions>
</Saving>
<Checking ID="100047" Balance="1,000.00" Active="1" OverdraftAccount="100046">
<Transactions>
<Transaction ID="927" Type="Initial Deposit" Date="4/03/2009" Amount="1,000.00" NewBalance="1,000.00" />
<Transaction ID="1027" Type="Deposit" Date="4/04/2009" Amount="5,000.00" NewBalance="6,000.00" />
<Transaction ID="1127" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="5,500.00" />
<Transaction ID="1227" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="5,000.00" />
<Transaction ID="1327" Type="Withdraw" Date="4/04/2009" Amount="4,000.00" NewBalance="1,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="24" FirstName="Theodore" LastName="Roosey" Street="324 James Court" City="Seattle" State="WA" Zip="98105" HomePhone="206-874-8965" WorkPhone="206-789-8546" MobilePhone="206-945-6545" EMail="TRoosey@somewhere.net">
<Accounts>
<Saving ID="100048" Balance="29.00" Active="1">
<Transactions>
<Transaction ID="433" Type="Initial Deposit" Date="4/03/2009" Amount="9,000.00" NewBalance="9,000.00" />
<Transaction ID="533" Type="Deposit" Date="4/03/2009" Amount="9,000.00" NewBalance="18,000.00" />
<Transaction ID="633" Type="Deposit" Date="4/03/2009" Amount="3,000.00" NewBalance="21,000.00" />
<Transaction ID="733" Type="Deposit" Date="4/03/2009" Amount="10,000.00" NewBalance="31,000.00" />
<Transaction ID="833" Type="Withdraw" Date="4/03/2009" Amount="30,971.00" NewBalance="29.00" />
</Transactions>
</Saving>
<Checking ID="100049" Balance="2,500.00" Active="1" OverdraftAccount="100048">
<Transactions>
<Transaction ID="933" Type="Initial Deposit" Date="4/03/2009" Amount="4,000.00" NewBalance="4,000.00" />
<Transaction ID="1033" Type="Deposit" Date="4/04/2009" Amount="45,000.00" NewBalance="49,000.00" />
<Transaction ID="1133" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="48,500.00" />
<Transaction ID="1233" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="48,000.00" />
<Transaction ID="1333" Type="Withdraw" Date="4/04/2009" Amount="5,000.00" NewBalance="53,000.00" />
<Transaction ID="1433" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="52,500.00" />
<Transaction ID="1533" Type="Withdraw" Date="4/04/2009" Amount="50,000.00" NewBalance="2,500.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="25" FirstName="Kelly" LastName="Haim" Street="874 California Ave." City="Seattle " State="WA" Zip="98125" HomePhone="206-985-6958" WorkPhone="206-985-8645" MobilePhone="206-321-3255" EMail="KHaim@somewhere.net">
<Accounts>
<Saving ID="100050" Balance="99,000.00" Active="1">
<Transactions>
<Transaction ID="1833" Type="Initial Deposit" Date="4/04/2009" Amount="99,000.00" NewBalance="99,000.00" />
<Transaction ID="1933" Type="Deposit" Date="4/04/2009" Amount="200,000.00" NewBalance="299,000.00" />
<Transaction ID="2233" Type="Withdraw" Date="4/04/2009" Amount="200,000.00" NewBalance="99,000.00" />
</Transactions>
</Saving>
<Checking ID="100051" Balance="8,000.00" Active="1" OverdraftAccount="100050">
<Transactions>
<Transaction ID="435" Type="Initial Deposit" Date="4/03/2009" Amount="8,000.00" NewBalance="8,000.00" />
<Transaction ID="535" Type="Deposit" Date="4/03/2009" Amount="5,000.00" NewBalance="13,000.00" />
<Transaction ID="635" Type="Withdraw" Date="4/03/2009" Amount="5,000.00" NewBalance="8,000.00" />
<Transaction ID="735" Type="Withdraw" Date="4/03/2009" Amount="500.00" NewBalance="7,500.00" />
<Transaction ID="835" Type="Withdraw" Date="4/03/2009" Amount="5,000.00" NewBalance="2,500.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="26" FirstName="Mike" LastName="Jones" Street="234 MacArthur" City="Seattle " State="WA" Zip="98104" HomePhone="206-985-4152" WorkPhone="206-876-8976" MobilePhone="206-326-6523" EMail="MJones@somewhere.net">
<Accounts>
<Saving ID="100052" Balance="35,000.00" Active="1">
<Transactions>
<Transaction ID="1335" Type="Initial Deposit" Date="4/04/2009" Amount="31,000.00" NewBalance="31,000.00" />
<Transaction ID="1435" Type="Deposit" Date="4/04/2009" Amount="2,000.00" NewBalance="33,000.00" />
<Transaction ID="1535" Type="Deposit" Date="4/04/2009" Amount="1,000.00" NewBalance="34,000.00" />
<Transaction ID="1635" Type="Deposit" Date="4/04/2009" Amount="3,000.00" NewBalance="37,000.00" />
<Transaction ID="1735" Type="Withdraw" Date="4/04/2009" Amount="2,000.00" NewBalance="35,000.00" />
</Transactions>
</Saving>
<Checking ID="100053" Balance="12,000.00" Active="1" OverdraftAccount="100052">
<Transactions>
<Transaction ID="1835" Type="Initial Deposit" Date="4/04/2009" Amount="7,000.00" NewBalance="7,000.00" />
<Transaction ID="1935" Type="Deposit" Date="4/04/2009" Amount="5,000.00" NewBalance="12,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="27" FirstName="Dave" LastName="Robinson" Street="834 Return Ave." City="Seattle" State="WA" Zip="98101" HomePhone="206-698-9865" WorkPhone="206-986-8598" MobilePhone="206-865-7894" EMail="DRobinson@somewhere.net">
<Accounts>
<Saving ID="100054" Balance="17,000.00" Active="1">
<Transactions>
<Transaction ID="1318" Type="Initial Deposit" Date="4/04/2009" Amount="7,000.00" NewBalance="7,000.00" />
<Transaction ID="2319" Type="Deposit" Date="4/04/2009" Amount="1,000.00" NewBalance="8,000.00" />
<Transaction ID="3320" Type="Deposit" Date="4/05/2009" Amount="1,000.00" NewBalance="9,000.00" />
<Transaction ID="4321" Type="Deposit" Date="4/05/2009" Amount="10,000.00" NewBalance="19,000.00" />
<Transaction ID="5322" Type="Withdraw" Date="4/05/2009" Amount="2,000.00" NewBalance="17,000.00" />
</Transactions>
</Saving>
<Checking ID="100055" Balance="8,000.00" Active="1" OverdraftAccount="100054">
<Transactions>
<Transaction ID="440" Type="Initial Deposit" Date="4/03/2009" Amount="4,000.00" NewBalance="4,000.00" />
<Transaction ID="540" Type="Deposit" Date="4/03/2009" Amount="5,000.00" NewBalance="9,000.00" />
<Transaction ID="640" Type="Withdraw" Date="4/03/2009" Amount="500.00" NewBalance="8,500.00" />
<Transaction ID="740" Type="Withdraw" Date="4/03/2009" Amount="500.00" NewBalance="8,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="28" FirstName="Jim" LastName="Tiden" Street="389 Jones Ave." City="Seattle " State="WA" Zip="98108" HomePhone="206-659-8598" WorkPhone="206-986-1234" MobilePhone="206-896-6598" EMail="JTiden@somewhere.net">
<Accounts>
<Saving ID="100056" Balance="5,000.00" Active="1">
<Transactions>
<Transaction ID="1340" Type="Initial Deposit" Date="4/04/2009" Amount="5,000.00" NewBalance="5,000.00" />
</Transactions>
</Saving>
<Checking ID="100057" Balance="99,500.00" Active="1" OverdraftAccount="100056">
<Transactions>
<Transaction ID="1840" Type="Initial Deposit" Date="4/04/2009" Amount="80,000.00" NewBalance="80,000.00" />
<Transaction ID="1940" Type="Deposit" Date="4/04/2009" Amount="15,000.00" NewBalance="95,000.00" />
<Transaction ID="2040" Type="Deposit" Date="4/04/2009" Amount="5,000.00" NewBalance="100,000.00" />
<Transaction ID="2140" Type="Deposit" Date="4/04/2009" Amount="5,000.00" NewBalance="105,000.00" />
<Transaction ID="2240" Type="Withdraw" Date="4/04/2009" Amount="5,000.00" NewBalance="100,000.00" />
<Transaction ID="2340" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="99,500.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="29" FirstName="Aidan" LastName="Gross" Street="983 Woodbury" City="Seattle " State="WA" Zip="98104" HomePhone="206-987-5645" WorkPhone="206-856-1234" MobilePhone="206-987-8976" EMail="AGross@somewhere.net">
<Accounts>
<Saving ID="100058" Balance="115,000.00" Active="1">
<Transactions>
<Transaction ID="450" Type="Initial Deposit" Date="4/03/2009" Amount="4,000.00" NewBalance="4,000.00" />
<Transaction ID="550" Type="Deposit" Date="4/03/2009" Amount="54,000.00" NewBalance="58,000.00" />
<Transaction ID="650" Type="Deposit" Date="4/03/2009" Amount="67,000.00" NewBalance="125,000.00" />
<Transaction ID="750" Type="Deposit" Date="4/03/2009" Amount="10,000.00" NewBalance="135,000.00" />
<Transaction ID="850" Type="Withdraw" Date="4/03/2009" Amount="20,000.00" NewBalance="115,000.00" />
</Transactions>
</Saving>
<Checking ID="100059" Balance="90,500.00" Active="1" OverdraftAccount="100058">
<Transactions>
<Transaction ID="950" Type="Initial Deposit" Date="4/03/2009" Amount="100,000.00" NewBalance="100,000.00" />
<Transaction ID="1050" Type="Deposit" Date="4/04/2009" Amount="5,000.00" NewBalance="105,000.00" />
<Transaction ID="1150" Type="Withdraw" Date="4/04/2009" Amount="5,000.00" NewBalance="100,000.00" />
<Transaction ID="1250" Type="Withdraw" Date="4/04/2009" Amount="5,000.00" NewBalance="95,000.00" />
<Transaction ID="1350" Type="Withdraw" Date="4/04/2009" Amount="5,000.00" NewBalance="90,000.00" />
<Transaction ID="1450" Type="Deposit" Date="4/04/2009" Amount="500.00" NewBalance="90,500.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
<Customer ID="30" FirstName="Mark" LastName="Michaels" Street="234 Ideal Ave." City="Seattle " State="WA" Zip="98105" HomePhone="206-986-5986" WorkPhone="206-897-8965" MobilePhone="206-698-8965" EMail="MMich@somewhere.net">
<Accounts>
<Saving ID="100060" Balance="21,000.00" Active="1">
<Transactions>
<Transaction ID="1850" Type="Initial Deposit" Date="4/04/2009" Amount="7,000.00" NewBalance="7,000.00" />
<Transaction ID="1950" Type="Deposit" Date="4/04/2009" Amount="2,000.00" NewBalance="9,000.00" />
<Transaction ID="2050" Type="Deposit" Date="4/04/2009" Amount="2,000.00" NewBalance="11,000.00" />
<Transaction ID="2150" Type="Deposit" Date="4/04/2009" Amount="10,000.00" NewBalance="21,000.00" />
</Transactions>
</Saving>
<Checking ID="100061" Balance="6,000.00" Active="1" OverdraftAccount="100060">
<Transactions>
<Transaction ID="2350" Type="Initial Deposit" Date="4/04/2009" Amount="6,000.00" NewBalance="6,000.00" />
<Transaction ID="460" Type="Deposit" Date="4/03/2009" Amount="5,000.00" NewBalance="11,000.00" />
</Transactions>
</Checking>
<Saving ID="100062" Balance="3,000.00" Active="1">
<Transactions>
<Transaction ID="478" Type="Initial Deposit" Date="4/03/2009" Amount="7,000.00" NewBalance="7,000.00" />
<Transaction ID="578" Type="Deposit" Date="4/03/2009" Amount="3,000.00" NewBalance="10,000.00" />
</Transactions>
</Saving>
<Checking ID="100063" Balance="33,000.00" Active="1" OverdraftAccount="100062">
<Transactions>
<Transaction ID="978" Type="Initial Deposit" Date="4/03/2009" Amount="30,000.00" NewBalance="100,000.00" />
<Transaction ID="1078" Type="Deposit" Date="4/04/2009" Amount="3,000.00" NewBalance="33,000.00" />
</Transactions>
</Checking>
<Saving ID="100064" Balance="20,000.00" Active="1">
<Transactions>
<Transaction ID="480" Type="Initial Deposit" Date="4/03/2009" Amount="6,000.00" NewBalance="6,000.00" />
<Transaction ID="580" Type="Deposit" Date="4/03/2009" Amount="2,000.00" NewBalance="8,000.00" />
<Transaction ID="680" Type="Deposit" Date="4/03/2009" Amount="2,000.00" NewBalance="10,000.00" />
<Transaction ID="780" Type="Deposit" Date="4/03/2009" Amount="10,000.00" NewBalance="20,000.00" />
</Transactions>
</Saving>
<Checking ID="100065" Balance="10,000.00" Active="1" OverdraftAccount="100064">
<Transactions>
<Transaction ID="980" Type="Initial Deposit" Date="4/03/2009" Amount="80,000.00" NewBalance="80,000.00" />
<Transaction ID="1080" Type="Deposit" Date="4/04/2009" Amount="5,000.00" NewBalance="85,000.00" />
<Transaction ID="1180" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="84,500.00" />
<Transaction ID="1280" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="84,000.00" />
<Transaction ID="1380" Type="Withdraw" Date="4/04/2009" Amount="5,000.00" NewBalance="79,000.00" />
<Transaction ID="1480" Type="Withdraw" Date="4/04/2009" Amount="500.00" NewBalance="78,500.00" />
<Transaction ID="1580" Type="Withdraw" Date="4/04/2009" Amount="50,000.00" NewBalance="28,500.00" />
<Transaction ID="1680" Type="Withdraw" Date="4/04/2009" Amount="17,500.00" NewBalance="11,000.00" />
<Transaction ID="1780" Type="Withdraw" Date="4/04/2009" Amount="1,000.00" NewBalance="10,000.00" />
</Transactions>
</Checking>
</Accounts>
</Customer>
</Root>'
SELECT
tn.value('@ID', 'tinyint') AS TransactionID, -- taken from the <Transaction> node
an.value('@ID', 'varchar(10)') AS AccountID, -- taken from the <Account> node
tn.value('@Type', 'varchar(100)') AS TransactionType, -- taken from the <Transaction> node
cn.value('@CustomerID', 'int') AS CustomerID, -- taken from the <Customer> node
tn.value('@Date', 'varchar(10)') AS TransactionDate, -- taken from the <Transaction> node
tn.value ('@Amount', 'money') AS TransactionAmount, -- taken from the <Transaction> node
tn.value ('@NewBalance', 'money') AS NewBalance -- taken from the <Transaction> node
FROM
@XML.nodes ('/ROOT/Customer') as cust(cn)
cross apply
cn.nodes('Accounts/*') as acc(an)
cross apply
an.nodes('Transactions/Transaction') AS tra(tn)
February 15, 2011 at 5:17 pm
Rich Yarger (2/15/2011)
...And that is why I am a newbie!
Thanks again Lutz!
😉
You're welcome. 🙂
(I just got a little confused since you used it correctly before... I didn't wanted to come across rude or disrespectful. If so, I'm sorry!!)
February 15, 2011 at 5:22 pm
LutzM (2/15/2011)
Let's see if I can beat MM this time (by still being correct...):
You did - I am watching a film 😀
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 15, 2011 at 5:25 pm
LutzM (2/15/2011)
You can basically see the nodes reference as a "loose table definition" with a single xml column.Therefore, it's sometimes referenced as T(c) which is Table(column).
The one column itself holds another set of XML data that again can be referenced in a similar way.
So, the CROSS APPLY usually would be written as
CROSS APPLY Table.column.nodes('') AS YetAnotherTable(YetAnotherXmlColumn)
CROSS APPLY YetAnotherTable.YetAnotherXmlColumn.nodes('') AS Tx(cy)
When selecting data out of such an XML column, usually only the column name is referenced, since it needs to be unique with the XML "columns".
But to stay within the qualified naming convention, it better should be written as
*facepalm* D'oh. I completely missed that... thank you. It was the forced uniqueness that kept throwing me off on what it was doing.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 15, 2011 at 5:25 pm
Rich Yarger (2/15/2011)
Blaaaaaaaaaaaaaaaaaaaaaaaaaaah. . .Columns - not data. 🙁
You have to be careful about case sensitivity - ROOT changed to Root
SELECT
tn.value('@ID', 'int') AS TransactionID, -- taken from the <Transaction> node
an.value('@ID', 'varchar(10)') AS AccountID, -- taken from the <Account> node
tn.value('@Type', 'varchar(100)') AS TransactionType, -- taken from the <Transaction> node
cn.value('@CustomerID', 'int') AS CustomerID, -- taken from the <Customer> node
tn.value('@Date', 'varchar(10)') AS TransactionDate, -- taken from the <Transaction> node
tn.value ('@Amount', 'money') AS TransactionAmount, -- taken from the <Transaction> node
tn.value ('@NewBalance', 'money') AS NewBalance -- taken from the <Transaction> node
FROM
@XML.nodes ('/Root/Customer') as cust(cn)
cross apply
cn.nodes('Accounts/*') as acc(an)
cross apply
an.nodes('Transactions/Transaction') AS tra(tn)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 15, 2011 at 5:29 pm
Rich Yarger (2/15/2011)
Blaaaaaaaaaaaaaaaaaaaaaaaaaaah. . .Columns - not data. 🙁
DECLARE @XML xml;SET @XML ='
<Root>
<Customer ID="1" FirstName="John" LastName="Doe" Street="123, Fake Street" City="Anytown" State="NY" Zip="11280" HomePhone="555-123-4567"
...
</Transactions>
</Checking>
</Accounts>
</Customer>
</Root>'
SELECT
tn.value('@ID', 'tinyint') AS TransactionID, -- taken from the <Transaction> node
an.value('@ID', 'varchar(10)') AS AccountID, -- taken from the <Account> node
tn.value('@Type', 'varchar(100)') AS TransactionType, -- taken from the <Transaction> node
cn.value('@CustomerID', 'int') AS CustomerID, -- taken from the <Customer> node
tn.value('@Date', 'varchar(10)') AS TransactionDate, -- taken from the <Transaction> node
tn.value ('@Amount', 'money') AS TransactionAmount, -- taken from the <Transaction> node
tn.value ('@NewBalance', 'money') AS NewBalance -- taken from the <Transaction> node
FROM
@XML.nodes ('/ROOT/Customer') as cust(cn)
cross apply
cn.nodes('Accounts/*') as acc(an)
cross apply
an.nodes('Transactions/Transaction') AS tra(tn)
Those fixes are rather easy:
#1: Keep in mind: XQuery language is case sensitive. Your sample data start with Root, but the query references ROOT.
#2: Once that is fixed, you also need to change the data type of TransactionID from tinyint to int since there are values exceeding the limit.
#3: The CustomerID now is just ID. So you'll need to change those, too.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply