Extract values from XML in SQL Server

Question

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

Gottfried Lesigang · Accepted Answer · 2016-12-14 09:14:11Z

1

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

answered Dec 14, 2016 at 9:14

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

1 Comment

Dave Leech Over a year ago

Perfect thanks! I had tried with xml.Value but i was trying to read it like this, based on something i found online, (/ns:Import/ns:Invoice)[1]', 'varchar(max)') as 'Supplier' and this returned the comments instead. Thanks for the really speedy response!

Collectives™ on Stack Overflow

Extract values from XML in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related