Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help to process recursive XML, please Expand / Collapse
Author
Message
Posted Monday, June 30, 2014 2:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 4:41 PM
Points: 112, Visits: 278
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>'
Post #1587774
Posted Monday, June 30, 2014 2:22 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 1,310, Visits: 3,765
Quick thought, this article might get you started
Using the XMLTable() Function
Post #1587779
Posted Monday, June 30, 2014 2:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 4:41 PM
Points: 112, Visits: 278
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
Post #1587784
Posted Tuesday, July 1, 2014 12:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:29 AM
Points: 2,554, Visits: 1,615
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...
Post #1588171
Posted Tuesday, July 1, 2014 4:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 4:41 PM
Points: 112, Visits: 278
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.
Post #1588213
Posted Tuesday, July 1, 2014 4:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 1,785, Visits: 5,679
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw



  •   Post Attachments 
    HierarchyXML.PNG (31 views, 6.59 KB)
    Post #1588217
    Posted Tuesday, July 1, 2014 4:28 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 1:14 PM
    Points: 1,785, Visits: 5,679
    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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1588220
    Posted Tuesday, July 1, 2014 4:44 PM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Tuesday, July 1, 2014 4:41 PM
    Points: 112, Visits: 278
    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 :)
    Post #1588222
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse