Need help to process recursive XML, please

Question

Post reply

Need help to process recursive XML, please

BOR15K

SSCertifiable

Points: 5894
More actions
June 30, 2014 at 2:17 pm

#290525

I need a help / advice to process recursive XML, please:
I have a given XML which structure I cannot modify. I can build an XML COLLECTION as below to validate it so I know it is valid,
but my problem is how to extract the data that I need? Due to it been a recursive, I have no idea which level I should be
querying. In the working example below, if I want to find Employee 5 I have used a fixed
SELECT @x.value('/Application[1]/employeesRepository[1]/employees[1]/employee[1]/employeeEntry[3]/employee[1]/employeeEntry[3]/employee[1]/employeeEntry[1]/Name[1]/@val', 'VARCHAR(50)')
syntax, which obviously is of no use in an automated process.
Can one advise is there a dynamic way to extract an information for a specific employee / manager, please?
======================= working example (please create an xml collection before) ============
DECLARE @x XML (employees);
DECLARE @e VARCHAR(50);
SET @x =N'<?xml version="1.0" encoding="UTF-16" standalone="no" ?>
<Application>
<Header>
<Folders/>
</Header>
<employeesRepository>
<employees>
<employee>
<employeeType val="1"/>
<Name val="Manager 1" valUnicode="Perfect Manager"/>
<employeeEntry id="1">
<employeeType val="2"/>
<Name val="employee 1" valUnicode="employee ID1234"/>
</employeeEntry>
<employeeEntry id="2">
<employeeType val="2"/>
<Name val="employee 2" valUnicode="employee ID7987"/>
</employeeEntry>
<employeeEntry id="3">
<employeeType val="1"/>
<Name val="Manager 2" valUnicode="Manager ID2872"/>
<employee>
<employeeEntry id="4">
<employeeType val="2"/>
<Name val="employee 3" valUnicode="employee ID875"/>
</employeeEntry>
<employeeEntry id="5">
<employeeType val="2"/>
<Name val="employee 4" valUnicode="employee ID521"/>
</employeeEntry>
<employeeEntry id="6">
<employeeType val="1"/>
<Name val="Manager 4" valUnicode="Additional info"/>
<employee>
<employeeEntry id="7">
<employeeType val="2"/>
<Name val="employee 5" valUnicode="employee ID007"/>
</employeeEntry>
<employeeEntry id="8">
<employeeType val="2"/>
<Name val="employee 6" valUnicode="employee ID741"/>
</employeeEntry>
</employee>
</employeeEntry>
</employee>
</employeeEntry>
</employee>
<employee>
<employeeType val="2"/>
<Name val="Manager 10"/>
</employee>
</employees>
</employeesRepository>
</Application>'
SELECT @e = (SELECT @x.value('/Application[1]/employeesRepository[1]/employees[1]/employee[1]/employeeEntry[3]/employee[1]/employeeEntry[3]/employee[1]/employeeEntry[1]/Name[1]/@val', 'VARCHAR(50)'))
SELECT @e;
==================== Creating Schema Collection ===================
CREATE XML SCHEMA COLLECTION employees
AS
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Application">
<xs:complexType>
<xs:sequence>
<xs:element name="Header">
<xs:complexType>
<xs:sequence>
<xs:element name="Folders" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="employeesRepository">
<xs:complexType>
<xs:sequence>
<xs:element name="employees">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="employee">
<xs:complexType>
<xs:sequence>
<xs:element name="employeeType">
<xs:complexType>
<xs:attribute name="val" type="xs:unsignedByte" use="required" />
</xs:complexType>
</xs:element>
<xs:element name="Name">
<xs:complexType>
<xs:attribute name="val" type="xs:string" use="required" />
<xs:attribute name="valUnicode" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="employeeEntry">
<xs:complexType>
<xs:sequence>
<xs:element name="employeeType">
<xs:complexType>
<xs:attribute name="val" type="xs:unsignedByte" use="required" />
</xs:complexType>
</xs:element>
<xs:element name="Name">
<xs:complexType>
<xs:attribute name="val" type="xs:string" use="required" />
<xs:attribute name="valUnicode" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="employee">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="employeeEntry">
<xs:complexType>
<xs:sequence>
<xs:element name="employeeType">
<xs:complexType>
<xs:attribute name="val" type="xs:unsignedByte" use="required" />
</xs:complexType>
</xs:element>
<xs:element name="Name">
<xs:complexType>
<xs:attribute name="val" type="xs:string" use="required" />
<xs:attribute name="valUnicode" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="employee">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="employeeEntry">
<xs:complexType>
<xs:sequence>
<xs:element name="employeeType">
<xs:complexType>
<xs:attribute name="val" type="xs:unsignedByte" use="required" />
</xs:complexType>
</xs:element>
<xs:element name="Name">
<xs:complexType>
<xs:attribute name="val" type="xs:string" use="required" />
<xs:attribute name="valUnicode" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedByte" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedByte" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedByte" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick thought, this article might get you started

Using the XMLTable() Function

😎

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

Thank you.

From what I have seen, they end up with a same processing, meaning you need to know what level to look: SELECT @x.value('employees[1]/emp[2]/phone[1]','VARCHAR(20)') AS Phone

Arthur Olcot SSCertifiable Points: 6641 More actions · Answer 3

I'm probably not quite understanding what you need to achieve, but if you are looking for an <employeeEntry> based on a known attribute, like the ID or name for example, then you can target that by using something like the below. This returns the name of the employee with id=7 for example:

SELECT @x.value('(//employeeEntry[@id=7]/Name/@val)[1]', 'varchar(50)')

Have I misunderstood or does this help?

the XML that you have isn't that bad and there are plenty of things that can be done to work with the xml...

BOR15K SSCertifiable Points: 5894 More actions · Answer 4

Thank for the reply. I am getting "XQuery [value()]: Heterogeneous sequences are not allowed in '=', found 'xdt:anyAtomicType' and 'xs:unsignedByte'." when I try it.

That said, the requirement has been extended now and I need to locate the record by EmployeeType and valUnicode.

I thought may be it is possible to have a SELECT statement to populate a temp table with the data and then to use something like Oracle's CONNECT BY, please?

Thanks again - much appreciated.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

You can use a recursive CTE to generate a relationship table:

~~(Note: I could not get this to work with your schema collection though, for some reason it complains about the "value" method needing a singleton, but works fine without it)~~

WITH levels( Manager, Employee, Level, Node) AS

(

-- Anchor the query on the first employees node (Is this correct?)

SELECT cast(NULL AS varchar(100)) as Manager, nd.value('string((Name/@val)[1])','varchar(100)') AS Employee, 1 AS Level, nd.query('.') Node

FROM @x.nodes('(//employee)[1]') x(nd)

UNION ALL

-- Pull in all children that are employeeEntry nodes

SELECT levels.Employee, ed.value('(Name/@val)[1]','varchar(100)') AS Name, level+1 AS Level, ed.query('.')

FROM levels

CROSS APPLY levels.Node.nodes('child::node()/employeeEntry') x(ed)

UNION ALL

-- And all children that are employee/employeeEntry nodes

SELECT levels.Employee, ed.value('(Name/@val)[1]','varchar(100)') AS Name, level+1 AS Level, ed.query('.')

FROM levels

CROSS APPLY levels.Node.nodes('child::node()/employee/employeeEntry') x(ed)

)

SELECT Manager, Employee,Level

FROM levels

ORDER BY Level, Manager, Employee;

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

BOR15K (7/1/2014)
Thank for the reply. I am getting "XQuery [value()]: Heterogeneous sequences are not allowed in '=', found 'xdt:anyAtomicType' and 'xs:unsignedByte'." when I try it.
That said, the requirement has been extended now and I need to locate the record by EmployeeType and valUnicode.
I thought may be it is possible to have a SELECT statement to populate a temp table with the data and then to use something like Oracle's CONNECT BY, please?
Thanks again - much appreciated.

If you just want to pick out one employee then this works:

DECLARE @eType char(1) = '2';

DECLARE @unicode nvarchar(100) = 'employee ID741';

SELECT nd.query('.')

FROM @x.nodes('//employeeEntry[employeeType/@val[string() = sql:variable("@eType")] and Name/@valUnicode[string() = sql:variable("@unicode")]]') x(nd)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

BOR15K SSCertifiable Points: 5894 More actions · Answer 7

mister.magoo (7/1/2014)
You can use a recursive CTE to generate a relationship table:
~~(Note: I could not get this to work with your schema collection though, for some reason it complains about the "value" method needing a singleton, but works fine without it)~~
WITH levels( Manager, Employee, Level, Node) AS
(
-- Anchor the query on the first employees node (Is this correct?)
SELECT cast(NULL AS varchar(100)) as Manager, nd.value('string((Name/@val)[1])','varchar(100)') AS Employee, 1 AS Level, nd.query('.') Node
FROM @x.nodes('(//employee)[1]') x(nd)
UNION ALL
-- Pull in all children that are employeeEntry nodes
SELECT levels.Employee, ed.value('(Name/@val)[1]','varchar(100)') AS Name, level+1 AS Level, ed.query('.')
FROM levels
CROSS APPLY levels.Node.nodes('child::node()/employeeEntry') x(ed)
UNION ALL
-- And all children that are employee/employeeEntry nodes
SELECT levels.Employee, ed.value('(Name/@val)[1]','varchar(100)') AS Name, level+1 AS Level, ed.query('.')
FROM levels
CROSS APPLY levels.Node.nodes('child::node()/employee/employeeEntry') x(ed)
)
SELECT Manager, Employee,Level
FROM levels
ORDER BY Level, Manager, Employee;

Thanks a lot for you help ! Will learn it now 🙂