Extracting complex nested XML into a structured table

Question

I have the following in a SQL Server varchar column (so, have to CAST AS XML I guess):

Noteworthy: complex, inconsistent, variable number and type of elements in subordinate nodes.

What I would like to do is extract this into a table/dataset, in the following form:

|-------------|----------|------|-------------|------|----------------------|------------------------------------|-----------------------------------------------------|
| Displayname | editMode | name | namedColumn | type | SourceName           | Source App(s)                      | Target App(s)                                       |
|-------------|----------|------|-------------|------|----------------------|------------------------------------|-----------------------------------------------------|
|             |          |      |             |      |(AttributeSource.name)|(AttributeSource.ApplicationRef.name|AttributeTargets.AttributeTarget.name                |
|             |          |      |             |      |                      | AttributeSource.RuleRef.name       |AttributeTargets.AttributeTarget.ApplicationRef.name |
|             |          |      |             |      |                      |                                    |AttributeTargets.AttributeTarget.RuleRef.name        |
|-------------|----------|------|-------------|------|----------------------|------------------------------------|-----------------------------------------------------|

Basically, one row per ObjectAttribute, and then in the rightmost two columns ( Source App(s), Target App(s) ), "subrows" within the cells for each of the elements within the respective node.

My intuition is that this is extremely difficult, particularly in SQL server, so I have also included python/pandas in the tags (if anyone answers it from a python perspective, feel free to skip the database access part).

Is this possible?

user_14868997 · Answer

You can use XQuery .nodes in SQL to shred the nodes into separate rows. You use then use .value to get each value that you want.

Since you want to combine values into a single value with newlines, it's slightly more complicated, requiring the use of concat .query and .value, as well as a for iteration over the nodes you want to query

SELECT
  Displayname = x.attr.value('@displayName', 'varchar(100)'),
  editMode = x.attr.value('@editMode', 'varchar(100)'),
  name = x.attr.value('@name', 'varchar(100)'),
  namedColumn = '???',
  type = x.attr.value('@type', 'varchar(100)'),
  SourceName = x.attr.query('for $i in AttributeSource/@name return concat(./@name, "
")').value('text()[1]','nvarchar(1000)'),
  [Source App(s)] = x.attr.query('for $i in AttributeSource/*/Reference/@name return concat(@name, "
")').value('text()[1]','nvarchar(max)'),
  [Target App(s)] = x.attr.query('for $i in
      ( for $t in AttributeTargets/AttributeTarget
        return ($t/@name, $t/*/Reference/@name)
      )
      return concat(./@name, "
")
      ').value('text()[1]','nvarchar(max)')
FROM @xml.nodes('List/ObjectAttribute') x(attr);

db<>fiddle

The final one is the most complicated. Steps are as follows:

For each ObjectAttribute node that we pulled out using .nodes...
Take all AttributeTargets nodes and retrieve their /AttributeTarget child nodes.
Iterate over those in $t, returning for each the @name attribute, as well as descending to any child nodes -> Reference and returning also those @name attributes.
Iterating over those results in $i, concat it with a new line
Return all of that as XML using .query then convert it to nvarchar using .value.

If you didn't need the newlines, you could for example just do

SourceName = x.attr.query('AttributeSource/@name').value('text()[1]','nvarchar(1000)'),

Extracting complex nested XML into a structured table

1 Answers