T-SQL: Parse XML Data in from classes which inherit from a common base,-CodePudding

I'm trying to parse XML data in SQL Server. I have a XML column in a table, the XML stored in it can vary by type, but they all inherit from the same base type.

Row 1: has XML like so:

<Form>
  <TaskType>1</TaskType>
  --Other Properties ...
</Form>

Row 2: has XML like so:

<License>
  <TaskType>2</TaskType>
  --Other Properties ...
</License>

Normally I might parse XML with this T-SQL code snippet:

SELECT  
    xmlData.A.value('.', 'INT') AS Animal
FROM
    @XMLToParse.nodes('License/TaskType') xmlData(A)

This doesn't work since in a view since I'm dependent on the name to find the node.

How can I always find the TaskType XML element in my XML content?

CodePudding user response：

Apparently you can just interate the nodes like so without being aware of their name:

SELECT  xmlData.A.value('.', 'INT') AS Animal
FROM    @XMLToParse.nodes('node()/TaskType') xmlData(A)

CodePudding user response：

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT @tbl (xmldata) VALUES
(N'<Form>
    <TaskType>1</TaskType>
    <TaskName>Clone</TaskName>
    <!--Other XML elements-->
</Form>'),
(N'<License>
    <TaskType>2</TaskType>
    <TaskName>Copy</TaskName>
    <!--Other XML elements-->
</License>');
-- DDL and sample data population, end

SELECT ID
    , c.value('(TaskType/text())[1]', 'INT') AS TaskType
    , c.value('(TaskName/text())[1]', 'VARCHAR(20)') AS TaskName
FROM @tbl
    CROSS APPLY xmldata.nodes('/*') AS t(c);

Output

ID	TaskType	TaskName
1	1	Clone
2	2	Copy