CODEWITHSUNDEEP
sqlsql-server
Michael G
Michael G

Reputation: 6745

Selecting XML column without column alias wrapping

I have the following query that returns the following XML result set. Notice the nested <Configuration> elements in the Query 1 Results.

Question:

If I add a case statement, it removes the column name from the xml output. Which gives me the expected result. How can I do this without the case statement, or a nested select?
See Query 2 & Query 2 Results for an example.

Query 1:

   SELECT
        (SELECT
             q.NotificationQueueId
            ,nc.Configuration -- Provides unexpected result -- Nested <Configuration><Configuration /></Configuration>
            ,q.UserAdded
            ,q.DateAdded
            ,q.UserEdited
            ,q.DateEdited
            ,q.IsActive
        FROM Queue q
            INNER JOIN NotificationConfigurations nc ON nc.NotificationConfigurationId = q.NotificationConfigurationId
        FOR XML RAW ('NotificationItem'),TYPE)
    FOR XML RAW ('NotificationItems'),TYPE

Query 1 Results:

<NotificationItems>
  <NotificationItem NotificationQueueId="1" UserAdded="someone" DateAdded="2016-02-29T13:26:11.110" IsActive="1">
    <Configuration>
      <Configuration>
        <Email AddressList="[email protected]" Subject="blah blah blah">       
        </Email>
      </Configuration>
    </Configuration>
  </NotificationItem>
</NotificationItems>

Query 2:

   SELECT
        (SELECT
             q.NotificationQueueId
            ,CASE WHEN 1 = 1 THEN nc.Configuration END -- Provides expected result
            ,(SELECT nc.Configuration) -- Provides expected result
            ,q.UserAdded
            ,q.DateAdded
            ,q.UserEdited
            ,q.DateEdited
            ,q.IsActive
        FROM Queue q
            INNER JOIN NotificationConfigurations nc ON nc.NotificationConfigurationId = q.NotificationConfigurationId
        FOR XML RAW ('NotificationItem'),TYPE)
    FOR XML RAW ('NotificationItems'),TYPE

Query 2 Results

<NotificationItems>
  <NotificationItem NotificationQueueId="1" UserAdded="someone" DateAdded="2016-02-29T13:26:11.110" IsActive="1">
    <Configuration>
      <Email AddressList="[email protected]" Subject="blah blah blah">       
      </Email>
    </Configuration>
  </NotificationItem>
</NotificationItems>

Table definitions:

CREATE TABLE [dbo].[NotificationConfigurations](
    [NotificationConfigurationId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [Configuration] [xml] NOT NULL,
 )

CREATE TABLE [dbo].[Queue](
    [NotificationQueueId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [NotificationConfigurationId] [uniqueidentifier] NOT NULL,
    [UserAdded] [nvarchar](128) NOT NULL,
    [DateAdded] [datetime] NOT NULL,
    [UserEdited] [nvarchar](128) NULL,
    [DateEdited] [datetime] NULL,
    [IsActive] [bit] NOT NULL,
)

Example Configuration Xml:

<Configuration>
    <Email AddressList="[email protected]"
           Subject="blah blah blah">
    </Email>
</Configuration>

Upvotes: 0

Views: 191

Answers (1)

Y.B.
Y.B.

Reputation: 3586

Please see the example:

WITH t1 AS (
SELECT 1 AS ID UNION SELECT 2
), t2 AS (
SELECT 1 AS ID, CONVERT(XML, '
    <Configuration>
        <Email AddressList="[email protected]" Subject="blah blah blah">       
        </Email>
    </Configuration>
    ') As Configuration
)
SELECT (
    SELECT t1.ID,
        t2.Configuration,
        CASE WHEN 1 = 1 THEN t2.Configuration END,
        (SELECT nc.Configuration)
    FROM t1
    INNER JOIN t2 On t1.ID = t2.ID
    FOR XML RAW ('NotificationItem'),TYPE
) FOR XML RAW ('NotificationItems'),TYPE

Your Configuration field is already XML with <Configuration /> root tag. When selected it is being wrapped in an extra <Configuration /> tag following the field name.

You already have the solution through (SELECT nc.Configuration) which results in "anonymous" field that is included into result XML "as is", without field name wrapping tag. And this "nested query" does not actually cost anything.

Update: Of cause Configuration XML can be explicitly "unwrapped" before JOIN, but that would be much less efficient:

WITH t1 AS (
SELECT 1 AS ID UNION SELECT 2
), t2 AS (
SELECT 1 AS ID, CONVERT(XML, '
    <Configuration>
        <Email AddressList="[email protected]" Subject="blah blah blah">       
        </Email>
    </Configuration>
    ') As Configuration
)
SELECT (
    SELECT t1.ID,
        t2sub.Configuration
    FROM t1
    INNER JOIN (
        SELECT ID, sub.ConfigurationContents.query('.') AS Configuration
        FROM t2
        CROSS APPLY Configuration.nodes('/Configuration/child::node()') AS sub(ConfigurationContents) 
    ) t2sub ON t2sub.id = t1.id
    FOR XML RAW ('NotificationItem'),TYPE
) FOR XML RAW ('NotificationItems'),TYPE

Upvotes: 1

Related Questions