Extract value and attributes from xml in sql column

Question

I am working on a Microsoft SQL Server table that contains multiple datatypes, including a column for xml. Example:

CREATE TABLE [sites]
(
    [site_id] [int] NOT NULL,
    [organization_id] [int] NULL,
    [site_creationDate] [datetime] NULL,
    [site_active] [bit] NULL,
    [site_activatedBy] [varchar](20) NULL,
    [contentModel_id] [int] NULL,
    [site_settings] [xml] NULL
);

Here's what the XML looks like in the [site_settings] column:

Example 1:

<settings>
  <s key="hasinventorynotifier" value="0" type="BIT" />
  <s key="displayinstocknotifier" value="1" type="BIT" />
  <s key="usesimplequoteform" type="BIT" value="1" />
</settings>

Example 2:

<settings>
  <s key="isvisionimpaired" type="Bit" value="0" />
  <s key="insurancerequesturl" type="String" value="" />
  <s key="haswheelstudio" type="Bit" value="0" />
  <s key="hasironfunnel" type="Bit" value="0" />
  <s key="haselasticsearch" type="Bit" value="0" />
  <s key="hasinventorynotifier" type="Bit" value="0" />
  <s key="themeconfigurationjson" type="String" value="{&amp;quot;siteHeader&amp;quot;:{&amp;quot;scheme&amp;quot;:&amp;quot;light&amp;quot;,&amp;quot;headerLinks&amp;quot;:false,&amp;quot;navType&amp;quot;:&amp;quot;expanded&amp;quot;},&amp;quot;body&amp;quot;:{&amp;quot;grid&amp;quot;:&amp;quot;compact&amp;quot;},&amp;quot;siteFooter&amp;quot;:{&amp;quot;scheme&amp;quot;:&amp;quot;light&amp;quot;},&amp;quot;logo&amp;quot;:{&amp;quot;enabled&amp;quot;:true,&amp;quot;imageUrl&amp;quot;:&amp;quot;//cdnmedia.example.com/images/organizations/7a1fd181-1154-4cb6-8c43-a6f354b9f824/logo/dealer-logo.gif?v=1455044570132&amp;quot;}}" />
  <s key="hasautoquote" type="Bit" value="0" />
  <s key="usesimplequoteform" type="Bit" value="1" />
  <s key="overrideinventorysearchtitle" type="Bit" value="1" />
  <s key="overrideshowcasetitle" type="Bit" value="1" />
  <s key="financingexternalurl" type="String" value="" />
  <s key="disablemakeofferrequest" type="Bit" value="0" />
  <s key="enablereceiveoffers" type="Bit" value="1" />
  <s key="vehiclehistoryurl" type="String" value="" />
  <s key="displayinstocknotifier" type="Bit" value="1" />
  <s key="disableautoresponseemail" type="Bit" value="0" />
  <s key="enablesolditemsremoval" type="Bit" value="0" />
  <s key="usefinancingexternalurl" type="Bit" value="0" />
</settings>

As you can see from the above examples, the xml column is not very consistent from row to row and do not display all the "keys". (Note, I did not design this database.)

I want to create a view that shows all the columns, but also extracts some values from the xml structure in the [site_settings] column and display them as new columns in my view. I think our xml is kind of funky, as all the examples I've googled on trying to do this have failed for me.

Here's what kind of what I want:

CREATE VIEW [vw_Sites]
AS 
    SELECT 
        [site_id], [organization_id],
        [site_creationDate], [site_active], [site_activatedBy],
        [contentModel_id], [hasinventorynotifier],
        [displayinstocknotifier], [usesimplequoteform],
        [isvisionimpaired]  (and more xml keys, etc...)
    FROM 
        [sites] WITH (NOLOCK)
GO

I've tried a few methods of extracting via selecting the value of the column in these queries below, but the results come back NULL or blank.

SELECT 
    [site_settings].value('usesimplequoteform[1]','bit') AS Test1,
    [site_settings].value('settings[1]','varchar(10)') AS Test2,
    [site_settings].value('(s[@key="usesimplequoteform"]/@value)[1]','int') AS Test3
FROM
    [sites]

Alternatively, a colleague of mines suggested using the following case statements, which do work, but I was wondering if there was a better way. Case example below:

SELECT 
    CASE WHEN CHARINDEX('usesimplequoteform" type="Bit" value="1"',CONVERT(VARCHAR(MAX),site_settings),1) > 0 THEN 1 
            WHEN CHARINDEX('usesimplequoteform" value="1" type="Bit"',CONVERT(VARCHAR(MAX),site_settings),1) > 0 THEN 1
            ELSE 0 
       END AS useSimpleQuoteForm
FROM [sites]

But does anyone have a better way? The above case examples will only work on bit variables, and some of them are of type string (varchar). Any assistance would be greatly appreciated!

Clarification for output

Basically, the output would be a view that contains all the current columns from table [sites] and additional columns for each of the keys that can be present in the [sites].[site_settings] column.

[site_id]   [organization_id]   [site_creationDate] [site_active]   [site_activatedBy]  [contentModel_id]   [hasinventorynotifier]  [displayinstocknotifier]    [usesimplequoteform]    [the keys etc in xml]
5036            4886            2016-01-04 10:16:01.860     1           system              8                       1                           0                           1               (value)
5037            2386            2015-01-05 11:29:23.880     1           system              2                       0                           0                           1               (value)

I've added an example of the output... basically, return all the normal columns, but also return extracted key values from the xml column. I'm going to check out your recent answer though, thanks! — Namkce
– Namkce, Commented May 4, 2016 at 21:24
Thx for the output example. As you need all columns side-by-side in one row, you must use the approach I took as the second. See my edited answer — Gottfried Lesigang
– Gottfried Lesigang, Commented May 4, 2016 at 21:28

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

This is a way to get all values out as is:

Your XML reduced:

DECLARE @xml XML=
'<settings>
  <s key="isvisionimpaired" type="Bit" value="0" />
  <s key="insurancerequesturl" type="String" value="" />
  <s key="haswheelstudio" type="Bit" value="0" />
</settings>'

SELECT a.b.value('@key','varchar(max)') AS s_key
      ,a.b.value('@type','varchar(max)') AS s_type
      ,a.b.value('@value','varchar(max)') AS s_value
FROM @xml.nodes('settings/s') AS a(b);

Another approach might be - if you know all the possible data - to write all of them one after the other:

SELECT @xml.value('(/settings/s[@key="isvisionimpaired"]/@value)[1]','bit') AS isvisionimpaired
      ,@xml.value('(/settings/s[@key="insurancerequesturl"]/@value)[1]','varchar(max)') AS insurancerequesturl
      ,@xml.value('(/settings/s[@key="haswheelstudio"]/@value)[1]','bit') AS haswheelstudio
      --all possible data here

In this case missing values will just return empty...

The advantage: You can specify the correct data type and use a "speaking" column name (in your case the key's name probably).

A third approach might be dynamic SQL where you generate the statement using all information from within your XML to generate a statement exactly the same as I did it above. Doing so you can specify column names and datatypes dynamically.

Finally you'd need an EXEC which makes it impossible to integrate this approach in ad-hoc queries such as a VIEW.

EDIT: This should be what you need:

According to your comment

SELECT *
      ,[site_settings].value('(/settings/s[@key="isvisionimpaired"]/@value)[1]','bit') AS isvisionimpaired
      ,[site_settings].value('(/settings/s[@key="insurancerequesturl"]/@value)[1]','varchar(max)') AS insurancerequesturl
      ,[site_settings].value('(/settings/s[@key="haswheelstudio"]/@value)[1]','bit') AS haswheelstudio
      --all possible data here
FROM [sites]

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 4, 2016 at 20:51

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

3 Comments

Namkce Over a year ago

Thank you very much! your 2nd code block example was the piece I was looking for! I guess I was really close with my "Test 3" column example, but you figured it out for me. Thanks!

Gottfried Lesigang Over a year ago

@Namkce Glad to read this! If you need help on getting the JSON part decoded, place another question and poste me the link here. Happy Coding!

Gottfried Lesigang Over a year ago

@Namkce, Yes, your "Test3" was really close. Only the "/settings/" was missing...

Collectives™ on Stack Overflow

Extract value and attributes from xml in sql column

1 Answer 1

EDIT: This should be what you need:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT: This should be what you need:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related