locked
XML Info for every row RRS feed

  • Question

  • Hello, 

    I need your help to store every row for a table as xml at once and partition by invoice head id. 

    I have two tables invoiceHeader and invoiceDetails, but xml elements is different for every invoice type, it's not the same for every invoice. 

    For example:

    SELECT 
    lineNumber, quantity, measurementUnit, invoiceDetailType, netValue, vatCategory, vatAmount, vatExemptionCategory
    FROM invoiceDetails 
    FOR XML RAW ('invoiceDetails'), ELEMENTS;

    I need to know if there is a more efficient way except to select every invoice as XML PATH and insert then query result in a column (datatype as XML). Perhaps if i can do that in once for all rows.

    Thanks you. 

    Sunday, November 8, 2020 12:20 AM

All replies

  • Hello,

    Seems like a better idea would be to only create the XML when needed rather than storing the data in the tables as XML.

    For example using Microsoft NorthWind database order and order details (OrderID need not be there per-say).

    DECLARE @OrdId int = 10248
    SELECT O.OrderID, 
           O.CustomerIdentifier, 
           O.EmployeeID, 
           O.OrderDate, 
           O.RequiredDate, 
           O.ShippedDate, 
           O.ShipVia, 
           O.Freight, 
           O.ShipAddress, 
           O.ShipCity, 
           O.ShipRegion, 
           O.ShipPostalCode, 
           O.ShipCountry
    FROM Orders AS O
         INNER JOIN OrderDetails AS OD ON O.OrderID = OD.OrderID 
    WHERE O.OrderID = @OrdId 
    FOR XML PATH('Ord'), ROOT('CustomerOrders')
    

    We get the following

    <CustomerOrders>
      <Ord>
        <OrderID>10248</OrderID>
        <CustomerIdentifier>56</CustomerIdentifier>
        <EmployeeID>5</EmployeeID>
        <OrderDate>2015-07-04T00:00:00</OrderDate>
        <RequiredDate>2015-08-01T00:00:00</RequiredDate>
        <ShippedDate>2015-07-16T00:00:00</ShippedDate>
        <ShipVia>3</ShipVia>
        <Freight>32.3800</Freight>
        <ShipAddress>59 rue de l'Abbaye</ShipAddress>
        <ShipCity>Reims</ShipCity>
        <ShipPostalCode>51100</ShipPostalCode>
        <ShipCountry>France</ShipCountry>
      </Ord>
      <Ord>
        <OrderID>10248</OrderID>
        <CustomerIdentifier>56</CustomerIdentifier>
        <EmployeeID>5</EmployeeID>
        <OrderDate>2015-07-04T00:00:00</OrderDate>
        <RequiredDate>2015-08-01T00:00:00</RequiredDate>
        <ShippedDate>2015-07-16T00:00:00</ShippedDate>
        <ShipVia>3</ShipVia>
        <Freight>32.3800</Freight>
        <ShipAddress>59 rue de l'Abbaye</ShipAddress>
        <ShipCity>Reims</ShipCity>
        <ShipPostalCode>51100</ShipPostalCode>
        <ShipCountry>France</ShipCountry>
      </Ord>
      <Ord>
        <OrderID>10248</OrderID>
        <CustomerIdentifier>56</CustomerIdentifier>
        <EmployeeID>5</EmployeeID>
        <OrderDate>2015-07-04T00:00:00</OrderDate>
        <RequiredDate>2015-08-01T00:00:00</RequiredDate>
        <ShippedDate>2015-07-16T00:00:00</ShippedDate>
        <ShipVia>3</ShipVia>
        <Freight>32.3800</Freight>
        <ShipAddress>59 rue de l'Abbaye</ShipAddress>
        <ShipCity>Reims</ShipCity>
        <ShipPostalCode>51100</ShipPostalCode>
        <ShipCountry>France</ShipCountry>
      </Ord>
    </CustomerOrders>


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    My GitHub code samples
    GitHub page

    Sunday, November 8, 2020 1:18 AM
  • Hello ,

    Thanks you for your response. Why is better to create the XML when needed? I wanted to store xml in database in order to avoid a delay creating one by one the invoices.  

    And I think that will be a delay because for every invoice I must do a select for XML PATH because every invoice has different elements as XML but all invoices must be on the same root finally. 


    • Edited by Mary1982 Sunday, November 8, 2020 1:29 AM
    Sunday, November 8, 2020 1:26 AM
  • First off I don't know what you mean by delay as when SQL-Server databases and tables are constructed properly with proper indices and well formed SQL there really will not be slowness with SQL operations.

    In regards to different elements, going back to a real formed design for a database that will not matter.

    Next up, to query XML stored in tables a) means one can not easily view data outside an application b) queries are more complex in nature.

    With that said, if you are hard fast on using XML all I can say is best of luck with this endeavor. 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    My GitHub code samples
    GitHub page

    Sunday, November 8, 2020 2:17 AM
  • If you create a function that takes the ID and returns the XML for the corresponding row (probably using WHERE id=…), then you can define a “Computed Column” in Table Designer that calls this function. You will not need to insert and update the XML, because it will be computed when needed and will reflect the current values.

    Sunday, November 8, 2020 8:50 AM