Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

OPENXML issue Expand / Collapse
Author
Message
Posted Saturday, February 12, 2011 3:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:08 PM
Points: 485, Visits: 1,363
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.
Post #1063042
Posted Saturday, February 12, 2011 4:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 6,823, Visits: 13,267
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).




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1063054
Posted Saturday, February 12, 2011 5:26 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:08 PM
Points: 485, Visits: 1,363
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!
Post #1063057
Posted Saturday, February 12, 2011 6:18 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:54 AM
Points: 1,778, Visits: 5,729
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1063068
    Posted Sunday, February 13, 2011 12:54 PM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Today @ 10:08 PM
    Points: 485, Visits: 1,363
    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!
    Post #1063180
    Posted Monday, February 14, 2011 9:55 PM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Today @ 10:08 PM
    Points: 485, Visits: 1,363
    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!
    Post #1063990
    Posted Tuesday, February 15, 2011 2:43 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 8:54 AM
    Points: 1,778, Visits: 5,729
    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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1064112
    Posted Tuesday, February 15, 2011 7:40 AM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Today @ 10:08 PM
    Points: 485, Visits: 1,363
    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!
    Post #1064291
    Posted Tuesday, February 15, 2011 7:51 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 8:54 AM
    Points: 1,778, Visits: 5,729
    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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1064301
    Posted Tuesday, February 15, 2011 8:03 AM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Today @ 10:08 PM
    Points: 485, Visits: 1,363
    Thank you! If it takes you a few hours, no worries. I just appreicate the help!

    Post #1064313
    « Prev Topic | Next Topic »

    Add to briefcase 1234»»»

    Permissions Expand / Collapse