T-SQL Shred Second Level XML Nodes into Multiple Rows

Question

I feel like this should be something very simple, but I can't seem to figure it out.

Given the following XML:

<Responses>
  <Response>
    <ResponseId>1</ResponseId>
    <DateCreated>2016-02-01T18:26:17Z</DateCreated>
    <Detail>
      ...
    </Detail>
  </Response>
  <Response>
    <ResponseId>2</ResponseId>
    <DateCreated>2016-02-01T18:33:28Z</DateCreated>
    <Detail>
      ...
    </Detail>
  </Response>
  <Response>
    <ResponseId>3</ResponseId>
    <DateCreated>2016-02-12T19:33:48Z</DateCreated>
    <Detail>
      ...
    </Detail>
  </Response>
  <Response>
    <ResponseId>4</ResponseId>
    <DateCreated>2016-02-15T18:21:09Z</DateCreated>
    <Detail>
      ...
    </Detail>
  </Response>
  <Response>
    <ResponseId>5</ResponseId>
    <DateCreated>2016-02-15T19:04:07Z</DateCreated>
    <Detail>
      ...
    </Detail>
  </Response>
</Responses>

I am expecting results like below:

I am able to retrieve the elements per node just fine (ie. ResponseId, DateCreated, and Detail resulting in five rows with 3 columns each), but I need to return the full response node xml.

Currently, the only solution I can think of is to manually re-build the desired results after retrieving the ResponseId, DateCreated, and Detail, but that seems like a messy and error-prone option to me.

score 1 · Accepted Answer · answered Apr 13 '17 at 10:56

You can use .nodes() and .query()

DECLARE @xml XML=
N'<Responses>
  <Response>
    <ResponseId>1</ResponseId>
    <DateCreated>2016-02-01T18:26:17Z</DateCreated>
    <Detail>
    </Detail>
  </Response>
  <Response>
    <ResponseId>2</ResponseId>
    <DateCreated>2016-02-01T18:33:28Z</DateCreated>
    <Detail>
    </Detail>
  </Response>
  <Response>
    <ResponseId>3</ResponseId>
    <DateCreated>2016-02-12T19:33:48Z</DateCreated>
    <Detail>
    </Detail>
  </Response>
  <Response>
    <ResponseId>4</ResponseId>
    <DateCreated>2016-02-15T18:21:09Z</DateCreated>
    <Detail>
    </Detail>
  </Response>
  <Response>
    <ResponseId>5</ResponseId>
    <DateCreated>2016-02-15T19:04:07Z</DateCreated>
    <Detail>
    </Detail>
  </Response>
</Responses>';

--The select

SELECT One.Resp.query(N'.')
FROM @xml.nodes(N'/Responses/Response') AS One(Resp)

Shortly after posting this question, I had found another solution that involved a correlated query, but this is _**so**_ much more elegant! I'm relatively new to using XML in SQL queries, so I'm still learning about the XQuery syntax. — Eric, Apr 13 '17 at 11:08

T-SQL Shred Second Level XML Nodes into Multiple Rows

1 Answers1

Linked