February 12, 2011 at 3:11 pm
Hello -
I'm working on a project for class, and have come across an issue that I am having difficulties in troubleshooting. I am fairly new to working with XML, so please forgive what is probably a very "easy" issue. Here is the code I am trying to Shred (for right now, I am just testing to get the Customer information. Other sections will follow once I am successful in getting this to work. My points of reference thus far for constructing the code come from the BOL in 2008).
DECLARE @DocHandle int
DECLARE @XMLdocument varchar(1000)
SET @XMLdocument = N'<ROOT>
<Customer 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>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XMLdocument
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
WITH (CustomerID int,
CustomerFirstName nvarchar(20),
CustomerLastName nvarchar(30),
Street nvarchar(50),
City nvarchar(20),
[State] nchar(2),
ZipCode nchar(10),
Email nvarchar(30),
HomePhone nchar(12),
WorkPhone nchar(12),
CellPhone nchar(12))
EXEC sp_xml_removedocument @DocHandle
The error I am receiving from this is as follows:
The XML parse error 0xc00ce501 occurred on line number 2, near the XML text "<Customer 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">".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Missing equals sign between attribute and attribute value.'.
Msg 8179, Level 16, State 5, Line 26
Could not find prepared statement with handle 0.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.
Any insight into whether I have my understanding of Elements and Attributes all messed up would be greatly appreciated. I have tried using Attributes as a default as well as not, and I get the same result (regardless to the flag of 1, 2 or nothing).
Thank you all in advance!
P.S. The Hex coloring out here is not working. :w00t:
February 12, 2011 at 4:36 pm
There are two issues:
#1: in line 1 you have [Customer Customer ID="1" ] There seems to be an additional blank after the second "Customer". It should either be Customer_ID or CustomerID. Then the error will be different. It's not that you have a messed up understanding of elements and attributes. It's more an issue of yor sample data: you cannot have an additional level other than nodes -> elements -> attributes. But the sample data does.
#2: @XMLdocument is of varchar(1000). But the data you want to store in that variable will exceed the limit. Change it to varchar(2000) or nvarchar(2000) or even varchar(1200) and you'll get the expected result.
I know, this is not obvious based on the error message. But try a simple LEN(<insert the content of @XMLdocument here> ) and you'll notice that it exceeds the limit of varchar(1000).
February 12, 2011 at 5:26 pm
That was it! Lutz, thank you again for your help! I am going to now use some variables to put the columns into a format for import, and will give that LEN(<xml>) statement a look-see once I get this all nice and cleaned up (to see how many characters it is in full). I initially was using nvarchar(MAX), but went with the example in the BOL instead.
Thank you again!
February 12, 2011 at 6:18 pm
Hi,
Please also consider using the XML datatype instead of XmlDocuments - it is much easier to work with and faster!
Here is your select reworked to use an XMl datatype.
Also, please note that XML is case-sensitive and so specifying "Email" instead of "EMail" will not work.
Try this out and see if it is easier to work with:
DECLARE @XML xml;
SET @XML = N'<ROOT>
<Customer CustomerID="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>
</ROOT>'
SELECT
customer.value ('@CustomerID', 'int') AS CustomerID,
customer.value ('@FirstName', 'nvarchar(20)') AS CustomerFirstName,
customer.value ('@LastName', 'nvarchar(30)') AS CustomerLastName,
customer.value ('@Street', 'nvarchar(50)') AS Street,
customer.value ('@City', 'nvarchar(20)') AS City,
customer.value ('@State', 'nchar(2)') AS [State],
customer.value ('@Zip', 'nchar(10)') AS ZipCode,
customer.value ('@EMail', 'nvarchar(30)') AS Email,
customer.value ('@HomePhone', 'nchar(12)') AS HomePhone,
customer.value ('@WorkPhone', 'nchar(12)') AS WorkPhone,
customer.value ('@MobilePhone', 'nchar(12)') AS CellPhone
FROM
@XML.nodes ('/ROOT/Customer') AS x (customer)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 13, 2011 at 12:54 pm
Ah! That is SWEET! OK - I am going to get to work on this now to include the other pieces, so I may get this into my Transactions tbl.
Thank you Mister Magoo, and thank all of you for the assistance!
February 14, 2011 at 9:55 pm
Hi Mister Magoo - If I may, I'd like to run this by you!
I am trying now to include all the other elements and attributes, but am running into trouble. Perhaps I assume a bit too much here, but here is my code and the error I am getting, as I try to Shred the rest out...
DECLARE @XML xml;
SET @XML = N'<ROOT>
<Customer CustomerID="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>
<Savings 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>
</Savings>
<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>
</ROOT>'
SELECT
savings.value ('@Savings ID', 'int') AS AccountID,
transactions.value ('@Transaction ID', 'tinyint') AS TransactionTypeID,
customer.value ('@CustomerID', 'int') AS CustomerID,
transactions.value ('@Date', 'datetime') AS TransactionDate,
transactions.value ('@Amount', 'money') AS TransactionAmount,
transactions.value ('@NewBalance', 'money') AS NewBalance
FROM
@XML.nodes ('/ROOT/Customer/Accounts/Savings/Transactions') AS x (customer)
Here is the error...
Msg 4121, Level 16, State 1, Line 24
Cannot find either column "savings" or the user-defined function or aggregate "savings.value", or the name is ambiguous.
Any help you or anyone else out there might be able to provide would be much appreciated!
🙂
February 15, 2011 at 2:43 am
Is this the final requirement or will there be more after this (for example - you have specifically picked out Savings account - will that always be true?)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 15, 2011 at 7:40 am
Hi Mister Magoo!
No - the scenarios would also need to include checking as well as savings. On the same level - some will have an OverDraftAccountID, and some will not. Other than that - this is pretty much the scenario, and I have many records in succession after this one. Do I need to do whatever the fix is for each? Or is it one statement for all my XML records that I want to format for INSERT?
Many thanks!
February 15, 2011 at 7:51 am
I was asking because you had coded it just for Savings, but I needed to know if it should be processing all accounts ...
I might not be able to post an update to the code for a few hours, but will certainly do that later if no-one else has jumped i to help.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 15, 2011 at 8:03 am
Thank you! If it takes you a few hours, no worries. I just appreicate the help!
🙂
February 15, 2011 at 10:21 am
Something like this?
SELECT
customer.value ('@CustomerID', 'int') AS CustomerID,
customer.value ('@FirstName', 'nvarchar(20)') AS CustomerFirstName,
customer.value ('@LastName', 'nvarchar(30)') AS CustomerLastName,
customer.value ('@Street', 'nvarchar(50)') AS Street,
customer.value ('@City', 'nvarchar(20)') AS City,
customer.value ('@State', 'nchar(2)') AS [State],
customer.value ('@Zip', 'nchar(10)') AS ZipCode,
customer.value ('@EMail', 'nvarchar(30)') AS Email,
customer.value ('@HomePhone', 'nchar(12)') AS HomePhone,
customer.value ('@WorkPhone', 'nchar(12)') AS WorkPhone,
customer.value ('@MobilePhone', 'nchar(12)') AS CellPhone,
Savings.value ('@ID', 'int') AS AccountID,
Transactions.value ('@ID', 'int') AS AccountID
FROM
@XML.nodes ('/ROOT/Customer') AS x (customer)
CROSS APPLY x.customer.nodes('Accounts/Savings') AS y(Savings)
CROSS APPLY y.Savings.nodes('Transactions/Transaction') AS yt(Transactions)
February 15, 2011 at 11:00 am
Thanks Lutz 🙂
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 15, 2011 at 12:56 pm
Hi guys!
What I am needing to do is extract out the Transaction information. Basically I have a tbl called Transactions that has the following columns:
- TransactionID (PKey, Identity)
- AccountID
- TransactionTypeID
- CustomerID
- TransactionDate (Defaulted by GETDATE)
- TransactionAmount
- NewBalance
I need to get the transactions into this table, but obviously, that is going to take a combination of elements/attributes from this XML example. I have many other entries like this one, so I guess I need to know 2 things:
1. How to format for the columns listed above, and
2. Do I just need this one statement you all have been sharing with me? Or do I need to do this for every entry I need to get over to my Transactions tbl?
Many thanks!
February 15, 2011 at 3:51 pm
Hi,
Based on your latest post, this should give you an idea of how to extract the data...
If you have any questions about what this is doing, just ask.
SELECT
trans.value('@ID', 'tinyint') AS TransactionID, -- taken from the current node
trans.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
trans.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/Accounts/*/Transactions/Transaction') AS acc(trans)
-- this can be shortened (where it is safe to do so) to
-- @XML.nodes ('//Transaction') AS acc(trans)
-- but that is not as clear to read...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 15, 2011 at 3:54 pm
mister.magoo (2/15/2011)
Hi,Based on your latest post, this should give you an idea of how to extract the data...
If you have any questions about what this is doing, just ask.
SELECT
trans.value('@ID', 'tinyint') AS TransactionID, -- taken from the current node
trans.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
trans.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/Accounts/*/Transactions/Transaction') AS acc(trans)
-- this can be shortened (where it is safe to do so) to
-- @XML.nodes ('//Transaction') AS acc(trans)
-- but that is not as clear to read...
Sorry, I forgot to include a note about the Date you are extracting...
It is stored as a string, so you will notice I pulled it out as a varchar(10) to be safe. You will need to further convert that using the correct regional code to get it as a datetime - I could not guess what date format you are using in the xml from the sample given.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply