How to retrieve data from XML column in SQL

Question

I have the following table layout:

OrderDetails:

ItemID (PK, int, not null)
ItemName (nvarchar(450), null)
OrderID (FK, int, not null)
Discounts (nvarchar(max), null)

The Discounts column is declared nvarchar(max) but the data is really XML - no idea why it wasn't declared as XML. I need to query the table to show the OrderID, ItemName, prorated discounts, all other discounts, and total of prorated + all other discounts. Here is an example of some records.

ItemID  ItemName  OrderID     Discounts
8610    Item 1    4227        SEE XML 4227 BELOW
8615    Item 2    4227        <DocumentElement></DocumentElement> //no discounts for this row
8620    Item 3    9387        SEE XML 9387 BELOW

XML OrderId = 4227:

<DocumentElement>
  <DiscountsTable>
        <DiscountDisplayName>Bundle A</DiscountDisplayName>
        <DiscountValue>6.00</DiscountValue>
  </DiscountsTable>
  <DiscountsTable>
        <DiscountDisplayName>Bundle B</DiscountDisplayName>
        <DiscountValue>25.00</DiscountValue>
  </DiscountsTable>
</DocumentElement>

XML for OrderId = 9387:

<DocumentElement>
  <DiscountsTable>
        <DiscountDisplayName>Prorated Discount</DiscountDisplayName>
        <DiscountValue>6.45</DiscountValue>
  </DiscountsTable>
  <DiscountsTable>
        <DiscountDisplayName>Bundle A</DiscountDisplayName>
        <DiscountValue>5.61</DiscountValue>
  </DiscountsTable>
  <DiscountsTable>
        <DiscountDisplayName>Bundle B</DiscountDisplayName>
        <DiscountValue>23.39</DiscountValue>
  </DiscountsTable>
</DocumentElement>

So, What I need is a query that will return the ItemID, ItemName, aggregated prorated discounts, aggregated bundled discounts, and a total of the discounts added together (prorated + bundled = total discounts). For the 3 records above, the query result should look like this:

Item ID  Item Name Prorated Discounts    Other Discounts       Total Discounts
8610     Item 1    0.00                  31.00                 31.00
8615     Item 2    0.00                  0.00                  0.00
8620     Item 3    6.45                  29.00                 35.45

I've tried using the value() method but I get an error stating it can not be used with nvarchar(max). How do I parse the XML column to pull aggregated values?

Alex Kudryashev · Accepted Answer · 2016-08-15 19:12:00Z

You need to convert the column to XML first to use SQLXML features. Something like this.

;with tbl as (
select ItemID, ItemName, OrderID,
convert(xml, Discounts) as Discounts
from OrderDetails
)
select ItemID, ItemName, OrderID,
t.v.value('DiscountDisplayName[1]','varchar(100)') DiscountDisplayName,
t.v.value('DiscountValue[1]','float') DiscountValue
from tbl cross apply Discounts.nodes('DocumentElement/DiscountsTable') t(v)

Elaborate a little

;with tbl as (
select ItemID, ItemName, OrderID,
convert(xml, Discounts) as Discounts
from OrderDetails
),
tbl1 as (
select ItemID, ItemName, OrderID,
t.v.value('(DiscountsTable[DiscountDisplayName="Prorated Discount"]/DiscountValue)[1]','float') Prorated, -- filter
t.v.value('(DiscountsTable[DiscountDisplayName="Bundle A"]/DiscountValue)[1]','float') BundleA,
t.v.value('(DiscountsTable[DiscountDisplayName="Bundle B"]/DiscountValue)[1]','float') BundleB
from tbl cross apply Discounts.nodes('DocumentElement') t(v) --do not go deeper
)
select ItemID, ItemName, OrderID, isnull(Prorated, 0) Prorated,
isnull(BundleA, 0) + isnull(BundleB, 0) Other,
isnull(Prorated, 0) + isnull(BundleA, 0) + isnull(BundleB, 0) Total
from tbl1

Paweł Dyl · Accepted Answer · 2016-08-15 18:48:24Z

3

I would solve it as follows:

WITH Conv AS
(
    SELECT ItemID,ItemName,OrderID,CONVERT(xml,Discounts) XmlVal
    FROM Src
)
SELECT ItemID, ItemName, OrderID, Prorated, Other, Prorated+Other Total
FROM Conv
CROSS APPLY
(
    SELECT SUM(CASE X.exist('DiscountDisplayName[text() = "Prorated Discount"]')
               WHEN 1 THEN X.value('DiscountValue[1]', 'decimal(15,2)')
               ELSE 0 END) Prorated,
           SUM(CASE X.exist('DiscountDisplayName[text() = "Prorated Discount"]')
               WHEN 0 THEN X.value('DiscountValue[1]', 'decimal(15,2)')
               ELSE 0 END) Other
     FROM XmlVal.nodes('/DocumentElement/DiscountsTable') T(X)
) T(Prorated, Other)

Results:

ItemID      ItemName OrderID     Prorated     Other    Total
----------- -------- ----------- ------------ -------- ---------
8610        Item 1   4227        0.00         31.00    31.00
8615        Item 2   4227        NULL         NULL     NULL
8620        Item 3   9387        6.45         29.00    35.45

You can add COALESCE or ISNULL to remove NULLs.

answered Aug 15, 2016 at 18:48

Paweł Dyl

9,1831 gold badge13 silver badges28 bronze badges

4 Comments

ihatemash Over a year ago

Thanks for your help. Your answer does get the data I need. However, I'm still not sure how to integrate it with my existing query. Is there some way I can msg you directly to explain? My existing query is too long to post in the comment section.

ihatemash Over a year ago

or is there a way to store the result set from your answer into variable that I can join with my existing query? I would have to add an ID to your answer but I've already done that. So, really I need to know how to join your query result with my existing query.

Paweł Dyl Over a year ago

You can use SELECT Columns INTO newTable FROM ... syntax.

ihatemash Over a year ago

Got it working. Thank you so much. I've been working on this query for 2 days now.

Collectives™ on Stack Overflow

How to retrieve data from XML column in SQL

2 Answers 2

Elaborate a little

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Elaborate a little

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related