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.

Gottfried Lesigang · Accepted Answer · 2017-04-13 10:56:31Z

1

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)

answered Apr 13, 2017 at 10:56

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

1 Comment

Eric Over a year ago

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.

Collectives™ on Stack Overflow

T-SQL Shred Second Level XML Nodes into Multiple Rows

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related