SQL SELECT using XML input

Question

I've currently got a C# application that responds to HTTP requests. The body of the HTTP request (XML) is passed to SQL Server, at which time the database engine performs the correct instruction. One of the instructions is used to load information about Invoices using the id of the customer(InvoiceLoad):

<InvoiceLoad ControlNumber="12345678901">
   <Invoice>
      <CustomerID>[email protected]</CustomerID>
   </Invoice>
</InvoiceLoad>

I need to perform a SELECT operation against the invoice table (which contains the associated email address).

I've tried using:

SELECT 'Date', 'Status', 'Location' 
FROM Invoices 
WHERE Email_Address = Invoice.A.value(.)  

using an xml.nodes('InvoiceLoad/Invoice/CustomerId') Invoice(A)

command.

However, as this query may run THOUSANDS of times per minute, I want to make it as fast as possible. I'm hearing that one way to do this may be to use CROSS APPLY (which I have never used). Is that the solution? If not, how exactly would I go about making this query as fast as possible? Any and all suggestions are greatly appreciated!

marc_s · Accepted Answer · 2012-06-20 19:09:34Z

1

I don't see why you would need a call to .nodes() at all - from what I understand, each XML fragment has just a single entry - right?

So given this XML:

<InvoiceLoad ControlNumber="12345678901">
   <Invoice>
      <CustomerID>[email protected]</CustomerID>
   </Invoice>
</InvoiceLoad>

you can use this SQL to get the value of the <CustomerID> node:

DECLARE @xmlvar XML

SET @xmlvar = '<InvoiceLoad ControlNumber="12345678901">
   <Invoice>
      <CustomerID>[email protected]</CustomerID>
   </Invoice>
</InvoiceLoad>'

SELECT 
   @xmlvar.value('(/InvoiceLoad/Invoice/CustomerID)[1]', 'varchar(100)')

and you can join this against your customer table or whatever you need to do.

If you have the XML stored in a table, and you always need to extract that value from <CustomerID>, you could also think about creating a computed, persisted column on that table that would extract that e-mail address into a separate column, which you could then use for easy joining. This requires a little bit of work - a stored function taking the XML as input - but it's really quite a nice way to "surface" certain important snippets of data from your XML.

Step 1: create your function

CREATE FUNCTION dbo.ExtractCustomer (@input XML)
RETURNS VARCHAR(255)
WITH SCHEMABINDING
AS BEGIN
    DECLARE @Result VARCHAR(255)

    SELECT 
        @Result = @Input.value('(/InvoiceLoad/Invoice/CustomerID)[1]', 'varchar(255)') 

    RETURN @result
END

So given your XML, you get the one <CustomerID> node and extract its "inner text" and return it as a VARCHAR(255).

Step 2: add a computed, persisted column to your table

ALTER TABLE dbo.YourTableWithTheXML
ADD CustomerID AS dbo.ExtractCustomer(YourXmlColumnHere) PERSISTED

Now, your table that has the XML column has a new column - CustomerID - which will automagically contain the contents of the <CustomerID> as a VARCHAR(255). The value is persisted, i.e. as long as the XML doesn't change, it doesn't have to be re-computed. You can use that column like any other on your table, and you can even index it to speed up any joins on it!

edited Jun 20, 2012 at 19:09

answered Jun 20, 2012 at 19:00

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TelJanini Over a year ago

Thanks for the answer! However, what would I use when the XML schema is updated to allow for multiple email addresses. Unfortunately, this is slated to happen about 6 months from now.

marc_s Over a year ago

@TelJanini: is it a fixed max number? E.g. 1-3 e-mail addresses? Or is it basically any number of e-mail addresses?

TelJanini Over a year ago

Right now, it's only 1 max, but in 6 months I'll need to handle any number of email addresses. Thanks again for your help!

Collectives™ on Stack Overflow

SQL SELECT using XML input

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related