Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OPENXML issue


OPENXML issue

Author
Message
SQL_ME_RICH
SQL_ME_RICH
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 1591
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
LutzM
LutzM
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7889 Visits: 13559
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
SQL_ME_RICH
SQL_ME_RICH
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 1591
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>Wink 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!
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2801 Visits: 7854
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);




  • 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

  • SQL_ME_RICH
    SQL_ME_RICH
    SSChasing Mays
    SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

    Group: General Forum Members
    Points: 642 Visits: 1591
    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!
    SQL_ME_RICH
    SQL_ME_RICH
    SSChasing Mays
    SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

    Group: General Forum Members
    Points: 642 Visits: 1591
    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!
    :-)
    mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

    Group: General Forum Members
    Points: 2801 Visits: 7854
    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);




  • 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

  • SQL_ME_RICH
    SQL_ME_RICH
    SSChasing Mays
    SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

    Group: General Forum Members
    Points: 642 Visits: 1591
    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!
    mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

    Group: General Forum Members
    Points: 2801 Visits: 7854
    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);




  • 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

  • SQL_ME_RICH
    SQL_ME_RICH
    SSChasing Mays
    SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

    Group: General Forum Members
    Points: 642 Visits: 1591
    Thank you! If it takes you a few hours, no worries. I just appreicate the help!

    :-)
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search