Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    30

    Unanswered: Make use of an passed XML parameter

    I have tested the following code, and it works for me:

    DECLARE @XML XML
    SET @XML = '
    <DocumentElement>
    <data>
    <item code="ABCDEFG" quantity="1" sort="0" />
    <item code="XCFVGBF" quantity="1" sort="0" />
    <item code="ABCDEFG" quantity="10" sort="0" />
    </data>
    </DocumentElement>'

    SELECT SUM(x.qty * ISNULL(p.price_mn,0))
    FROM (SELECT [Code] = A.A.value('@code','varchar(20)'),
    [Qty] = A.A.value('@quantity','INT')
    FROM @xml.nodes('/DocumentElement/data/item') AS A(A)) X
    LEFT JOIN productprice_t P ON p.code_id = x.code


    The question is, how can I do this if the XML is in a different format & doesn't use any attributes?
    So it looks like this (which I'm getting from an ADO.Net datatable using .WriteXML):
    <DocumentElement>
    <data>
    <code>ABCDEFG</mercurycode>
    <quantity>1</quantity>
    <sort>0</sort>
    </data>
    <data>
    <mercurycode>XCDFEG</mercurycode>
    <quantity>2</quantity>
    <sort>0</sort>
    </data>
    <data>
    <code>ABCDEFG</mercurycode>
    <quantity>10</quantity>
    <sort>0</sort>
    </data>
    </DocumentElement>
    Last edited by rbb; 11-19-07 at 15:43. Reason: Made some progress

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Ah, the old stupid question. You do tend to receive one of these every couple of months. On this occasion, I would be delighted to point out the glaring mistake in your question.

    The question is, how can I do this if the XML is in a different format & doesn't use any attributes
    You would write a new procedure to handle this new XML format. I should ask though, must you really, really use XML for this kind of situation? Would it not be possible to just use tables with rows and columns?
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2003
    Posts
    30
    Quote Originally Posted by r123456
    Ah, the old stupid question. You do tend to receive one of these every couple of months. On this occasion, I would be delighted to point out the glaring mistake in your question.



    You would write a new procedure to handle this new XML format. I should ask though, must you really, really use XML for this kind of situation? Would it not be possible to just use tables with rows and columns?

    Wow. Your insight has been most helpful! Please elaborate on your suggestion to handle the following;

    A user has a shopping cart made up of two columns - Code & Quantity. The code column cannot be unique because the user needs to be allowed to sort and their cart as they see fit. Because of this, we must summarize the data first. The cost cannot be added to the cart at the same time, because for any given product, there are 10 prices, depending on the user, and the sponsor wants to only show preferred pricing. Now I want to take my cart (which can have hundreds of items) and get the total for it. How do you propose I pass this to SQL to get the total of the cart? BTW, the cart is stored as an ADO.NET 2.0 data table.

    I figure, why not make use of the .WriteXML function of the dataset? So in a few lines of code, I have it all working with 1 trip to the DB, and it only returns a single output parameter. And fortunately for me, I was able to get things to work before your stellar advice came through.

    Seems pretty efficient to me - but PLEASE show me a more efficient away! I surely hope you don’t want me to pass a list of my codes and return a dataset filled with codes & costs, and then have the client app do the math.

    Also, try typing in XML & .nodes or XML & .value into the search function here. YOU make use of the results – especially when you are new to XML handling in SQL.
    Last edited by rbb; 11-20-07 at 11:02.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I answered your question clearly. Perhaps in future I will omit any suggestions for improvements, as you appear to have attacked the suggestion and ignored my answer to your question.

    Your question asked, how to use SQL to process an XML document that represents information as the data of elements (of whichever other fancy XML term people use these days) rather than as data contained within an attribute of an element.

    The answer I gave was to suggest that you either amend your existing procedure or write a new one to extract the values from the XML document by querying the elements rather than the attributes of the elements. In other words, I suggested that you amend your stored procedure to reflect the changes made to the format of your document. I don't understand the difficulty, unless of course you were asking for the syntax to query an XML node?

    If this was the case, then I suggest you do a quick search on Google for information on how to query the data from an XML elements using SQL Server. Because, however, it is trivial to find such information, for example by using the SQL Server reference manual or Google, I came to the conclusion that your question was indeed a trick question, similar to somebody asking how to find information on Google. Perhaps it is now clear as to why I gave a largely flippant reply.

    From what I can understand about your requirements, the decision to use XML as the transport mechanism for your shopping cart data appears to be based on the fact that the .NET framework provides an easy to use method for converting the contents of a dataset to an XML stream, which can then be just as easily used as input to a stored procedure. Although this is easy to code, as you already pointed out, have you considered the performance implications and the increase in manual labour that will be required to manage these kinds of procedures?

    As the shopping cart data is stored in an in-memory structure, the dataset, and I assume that you do not periodically flush it to the DB, each user's shopping cart will be stored as an XML Document in the memory of the application server. For this to be practical, you will need to have an indexing mechanism that will allow you to quickly search and retrieve any item from any one of your users' shopping carts. As all of the information pertaining to a shopping cart is stored in XML, this means you will need to index hundreds of pieces of data, which themselves are stored as XML, for each shopping cart of each user. This will need to be developed, tested and the performance compared to that of a database index scan.

    In addition to the issue of searching the shopping cart data, you will need to consider whether you want to use the memory of your application server(s) for the storage of this data. I've seen many a system crash with only a half dozen users because of the size of each user's session state, or the amount of memory allocated by a shared cache structure to store their individual information.

    Next, consider the operation that will be required to calculate the total for a shopping cart. As you've already mentioned, you cannot insert the price directly into the shopping cart, as it depends on external variables which are stored in the database. Therefore, each and every time a user wants to view the total price of their shopping cart, you will need to search the index for that user's shopping cart, convert the XML into a format that can be used as an input parameter to the stored procedure, invoke the stored procedure, which will then have to parse the XML, store the results in a temporary memory structure, query the database to retrieve the price information, and then send the result back to the application. This to me is a compelling reason why not to store the shopping cart data in memory in the first place, but rather represent a shopping cart using a database table.

    You also mention that the code element of the shopping cart cannot be unique, as the user needs to be able to sort on it. I do not understand this at all. Columns can be sorted irrespective of whether the contained values are unique or not. Sorry, but I don't understand how can claim that it not possible to sort a column because the data may not be unique.

    My proposal to increase the efficiency of your process is, then, to therefore that you eliminate the use of XML and use tables with a few stored procedures to update and read the data. You will then have a process that is easy to develop, manage and monitor. If we compare the two approaches, in terms of components used, we can observe the reduced complexity of the database approach.

    Approach using XML:

    Required components and processes:
    - XML Documents
    - Custom indexing mechanism
    - Memory structures to store the documents
    - Stored procedures to process and extract information from the XML documents.
    - Well documented processes to ensure the XML schema always remains constant to all processes that will access it. This will require at minimum, the application's processes, the dataset write method and the database stored procedure.
    - Each time the user wishes to query their shopping cart, the system must send the HUGE XML document to the database, which will then parse it and return an answer. This step is rather involved.

    Required approach using the database:
    - One or two tables to represent the shopping cart
    - A few stored procedures to update a shopping cart, and to return information including the total cost of shopping cart. The advantage here is that, as you need to calculate cost using variables from other tables, you can do this easily as your shopping cart data will already exist in tables. No need to parse anything.
    - One or two methods in the application to communicate with the database.
    - And, you will significantly reduce the amount of memory that is required when dealing with a shopping cart.

    Databases were born to read and write data. I do not see a compelling reason to not use them for this purpose.

    Differing opinions will of course exist, but me, I will always use a database for any operations that involve data. And if disk I/O becomes a performance problem, I'll just buy an in-memory database such as Oracle times ten
    Last edited by r123456; 11-20-07 at 13:22.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Sep 2003
    Posts
    30
    I must admit that your second post is actually useful and contains solid information. I appreciate well thought out and documented suggestions & comments when I see them.

    However, please give me one more piece of advice. In regards to my original question, I wanted to know how to use the built in XML handing functionality of SQL to use nested elements as opposed to attributes. While I have figured out how to modify my XML to use attributes and resolve the issue, my original question is still unanswered. I would greatly appreciate sample code to clarify my understanding.

    I did try Google (and of course this site) for solutions an examples, but all the documentation I found used attributes, and not nested elements. My trouble was compounded because I did not know the proper verbiage to use in my searches. I used combinations of XML, .value, .nodes, xquery, input paramenter, and xml datatype all to no avail.

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I still find it difficult to believe that you haven't been able to find information using Google about how to query an XML document using SQL Server. But anyway, that's neither here nor there.

    Try
    http://www.simple-talk.com/sql/sql-s...l-programming/

    This link explains very clearly, under the heading of OPENXML, how to open an XML document and retrieve specific elements using SQL.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •