CODEWITHSUNDEEP
xmlsql-server-2014
Dave Leech
Dave Leech

Reputation: 77

Extract values from XML in SQL Server

Please could someone give some guidance on the most efficient way to extract some values from an XML column? I've tried using substring and charindex within a case statement but this gets really messy and isn't the best performer. I would have thought that there is an XML function which would work better but struggling to find any examples online that use XML anything like this;

<pro:Import xmlns:pro="http://thenamespace/">
<pro:Control DatabaseName="varchar" UserName="varchar" Password="varchar" Company="varchar" Version="varchar" ErrorHandlingMode="varchar" />
<pro:Invoice RegisterInvoiceEvenIfParentOrdersAreInvalid="Bool" Tray="Varchar" Template="varchar" Supplier="varchar" SupplierInvoiceNumber="int" InvoiceDate="datetime" GrossValue="decimal" TaxValue="decimal" ImageReference="uniqueidentifier">
    <pro:References>
        <pro:Reference Code="varchar" Value="datetime" />
        <pro:Reference Code="varchar" Value="varchar" />
    </pro:References>
    <pro:ParentOrders>
        <pro:ParentOrder OrderNumber="varchar" />
    </pro:ParentOrders>
    <pro:TaxDetails>
        <pro:Tax Band="varchar" Code="varchar" GrossValue="decimal" TaxValue="decimal" />
    </pro:TaxDetails>
    <pro:Comments>
        <pro:Comment>varchar</pro:Comment>
    </pro:Comments>
</pro:Invoice>

The values I want to extract will vary, as will the XML. This is one example but there may be times when the values i want to extract for some XML are not present in others.

Example:

There might always be a Supplier="xxxx" but not a SupplierInvoiceNumber="xxxx" and its position will vary.

For arguments sake though, lets say for this exercise i just want to pull out

Supplier="varchar" & SupplierInvoiceNumber="int"

Thanks all :)

~D

Upvotes: 0

Views: 224

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Your XML misses the closing final tag...

I've tried using substring and charindex within a case statement but this gets really messy and isn't the best performer.

It is a very bad idea to try to read from XML on string level...

Try it like this:

DECLARE @xml XML=
N'<pro:Import xmlns:pro="http://thenamespace/">
  <pro:Control DatabaseName="varchar" UserName="varchar" Password="varchar" Company="varchar" Version="varchar" ErrorHandlingMode="varchar" />
  <pro:Invoice RegisterInvoiceEvenIfParentOrdersAreInvalid="Bool" Tray="Varchar" Template="varchar" Supplier="varchar" SupplierInvoiceNumber="int" InvoiceDate="datetime" GrossValue="decimal" TaxValue="decimal" ImageReference="uniqueidentifier">
    <pro:References>
      <pro:Reference Code="varchar" Value="datetime" />
      <pro:Reference Code="varchar" Value="varchar" />
    </pro:References>
    <pro:ParentOrders>
      <pro:ParentOrder OrderNumber="varchar" />
    </pro:ParentOrders>
    <pro:TaxDetails>
      <pro:Tax Band="varchar" Code="varchar" GrossValue="decimal" TaxValue="decimal" />
    </pro:TaxDetails>
    <pro:Comments>
      <pro:Comment>varchar</pro:Comment>
    </pro:Comments>
  </pro:Invoice>
</pro:Import>';

--Clean

WITH XMLNAMESPACES(DEFAULT 'http://thenamespace/')
SELECT @xml.value('(/Import/Invoice/@Supplier)[1]','nvarchar(max)') AS Supplier
      ,@xml.value('(/Import/Invoice/@SupplierInvoiceNumber)[1]','nvarchar(max)') AS SupplierInvoiceNumber

--Lazy

SELECT @xml.value('(//@Supplier)[1]','nvarchar(max)') AS Supplier
      ,@xml.value('(//@SupplierInvoiceNumber)[1]','nvarchar(max)') AS SupplierInvoiceNumber

Upvotes: 1

Related Questions