Extract data from XML data type field in SQL Server

Question

I execute the following query on the ReportServer database.

SELECT ItemID, CAST(CAST(Content AS VARBINARY(MAX)) AS XML) Content
FROM dbo.Catalog
WHERE Type = 8

I want to extract the fields list of the Content column. The content column is XML data type and contains XML like this:

<SharedDataSet xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <DataSet Name="DataSet1">
    <Query>
      <DataSourceReference>DDSDB</DataSourceReference>
      <CommandText>SELECT z.AccessoryPercent
    ,CASE 
        WHEN z.AccessoryPercent&gt;0.20 Then 1 
        WHEN z.AccessoryPercent &gt;=0.10 THEN 0 
        ELSE -1
    end AS AccessoryState
FROM (
    SELECT (a.FixedPrice - a.NetSalesPrice)/a.FixedPrice AS AccessoryPercent
    FROM acc.Fact_Crm_Accessory a
    )z</CommandText>
    </Query>
    <Fields>
      <Field Name="AccessoryPercent">
        <DataField>AccessoryPercent</DataField>
        <rd:TypeName>System.Decimal</rd:TypeName>
      </Field>
      <Field Name="AccessoryState">
        <DataField>AccessoryState</DataField>
        <rd:TypeName>System.Int32</rd:TypeName>
      </Field>
    </Fields>
  </DataSet>
</SharedDataSet>

My expected result is:

   DataSetName      Field
   -------------------------------------
   DataSet1         AccessoryPercent
   DataSet1         AccesspryState

Thanks in advance

EDIT: I wrote the following query to easier work on query.

CREATE TABLE #t (data XML)

INSERT INTO #t 
VALUES('<SharedDataSet xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSet Name="DataSet1">
    <Query>
      <DataSourceReference>DDSDB</DataSourceReference>
      <CommandText>SELECT z.AccessoryPercent
    ,CASE 
        WHEN z.AccessoryPercent&gt;0.20 Then 1 
        WHEN z.AccessoryPercent &gt;=0.10 THEN 0 
        ELSE -1
    end AS AccessoryState
FROM (
    SELECT (a.FixedPrice - a.NetSalesPrice)/a.FixedPrice AS AccessoryPercent
    FROM acc.Fact_Crm_Accessory a
    )z</CommandText>
    </Query>
    <Fields>
      <Field Name="AccessoryPercent">
        <DataField>AccessoryPercent</DataField>
        <TypeName>System.Decimal</TypeName>
      </Field>
      <Field Name="AccessoryState">
        <DataField>AccessoryState</DataField>
        <TypeName>System.Int32</TypeName>
      </Field>
    </Fields>
  </DataSet>
</SharedDataSet>')

SELECT  *
FROM #t AS t

Yitzhak Khabinsky · Accepted Answer · 2020-04-17 04:38:35Z

(1) We need to take into account a default namespace.

(2) Second CROSS APPLY takes case of one-to-many relationship between dataset and its fields.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (xmldata XML);

INSERT INTO @tbl 
VALUES('<SharedDataSet xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSet Name="DataSet1">
    <Query>
      <DataSourceReference>DDSDB</DataSourceReference>
      <CommandText>SELECT z.AccessoryPercent
    ,CASE 
        WHEN z.AccessoryPercent&gt;0.20 Then 1 
        WHEN z.AccessoryPercent &gt;=0.10 THEN 0 
        ELSE -1
    end AS AccessoryState
FROM (
    SELECT (a.FixedPrice - a.NetSalesPrice)/a.FixedPrice AS AccessoryPercent
    FROM acc.Fact_Crm_Accessory a
    )z</CommandText>
    </Query>
    <Fields>
      <Field Name="AccessoryPercent">
        <DataField>AccessoryPercent</DataField>
        <TypeName>System.Decimal</TypeName>
      </Field>
      <Field Name="AccessoryState">
        <DataField>AccessoryState</DataField>
        <TypeName>System.Int32</TypeName>
      </Field>
    </Fields>
  </DataSet>
</SharedDataSet>');
-- DDL and sample data population, end

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition')
SELECT c.value('@Name','VARCHAR(30)') AS DataSetName      
    , f.value('@Name','VARCHAR(30)') AS Field      
FROM @tbl AS tbl
    CROSS APPLY tbl.xmldata.nodes('/SharedDataSet/DataSet') AS t(c)
    CROSS APPLY t.c.nodes('Fields/Field') AS f(f);

Output

+-------------+------------------+
| DataSetName |      Field       |
+-------------+------------------+
| DataSet1    | AccessoryPercent |
| DataSet1    | AccessoryState   |
+-------------+------------------+

Collectives™ on Stack Overflow

Extract data from XML data type field in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related