Post reply

XML Query for Hiearchy

  • August 24, 2010 at 1:26 pm

    #387649

    I have an XML web source that I need to use as a source for a report. The basic connection to the web source and configuring XML as a data source is all good. Where I'm running into trouble is defining the XML query to put the data together. Roughly, here's what it looks like:

    <ROOT>

    <SUMMARY_LEVEL_1>

    <DEPTID>42</DEPTID>

    <LEVEL>1</LEVEL>

    <DETAIL_LEVEL_1>

    <EMPID>1</EMPID>

    <NAME>Dude</NAME>

    </DETAIL_LEVEL_1>

    <DETAIL_LEVEL_1>

    <EMPID>2</EMPID>

    <NAME>NotDude</NAME>

    </DETAIL_LEVEL_1>

    <SUMMARY_LEVEL_2>

    <DEPTID>43</DEPTID>

    <LEVEL>2</LEVEL>

    <DETAIL_LEVEL_2>

    <EMPID>3</EMPID>

    <NAME>Duddette</NAME>

    </DETAIL_LEVEL_2>

    </SUMMARY_LEVEL_2>

    </SUMMARY_LEVEL_1>

    </ROOT>

    Basically the XML and the report should be the same. What's going on is the developer formatted the XML to reflect the department hiearchy instead of letting me try to do it within the report. So now I just need to pull the report.

    At this point, I'm getting the Level 1 stuff back just fine, but I can't seem to get the Level 2 stuff back at the same time as the Level 1 stuff.

    Any help on what the XML Query syntax would be?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 24, 2010 at 2:13 pm

    #1211284

    Grant,

    How does this do?

    declare @test-2 xml = '

    <ROOT>

    <SUMMARY_LEVEL_1>

    <DEPTID>42</DEPTID>

    <LEVEL>1</LEVEL>

    <DETAIL_LEVEL_1>

    <EMPID>1</EMPID>

    <NAME>Dude</NAME>

    </DETAIL_LEVEL_1>

    <DETAIL_LEVEL_1>

    <EMPID>2</EMPID>

    <NAME>NotDude</NAME>

    </DETAIL_LEVEL_1>

    <SUMMARY_LEVEL_2>

    <DEPTID>43</DEPTID>

    <LEVEL>2</LEVEL>

    <DETAIL_LEVEL_2>

    <EMPID>3</EMPID>

    <NAME>Duddette</NAME>

    </DETAIL_LEVEL_2>

    </SUMMARY_LEVEL_2>

    </SUMMARY_LEVEL_1>

    </ROOT>';

    select [SummaryLevel] = 1,

    [DeptID] = Summary.Data.value('../DEPTID[1]','integer'),

    [Level] = summary.data.value('../LEVEL[1]','integer'),

    [EmpID] = summary.data.value('EMPID[1]','integer'),

    [Name] = summary.data.value('NAME[1]','varchar(50)')

    FROM @test.nodes('/ROOT/SUMMARY_LEVEL_1/*') AS Summary(Data)

    WHERE summary.data.value('EMPID[1]','integer') IS NOT NULL

    UNION ALL

    select [SummaryLevel] = 2,

    [DeptID] = Summary.Data.value('../DEPTID[1]','integer'),

    [Level] = summary.data.value('../LEVEL[1]','integer'),

    [EmpID] = summary.data.value('EMPID[1]','integer'),

    [Name] = summary.data.value('NAME[1]','varchar(50)')

    FROM @test.nodes('/ROOT/*/SUMMARY_LEVEL_2/*') AS Summary(Data)

    WHERE summary.data.value('EMPID[1]','integer') IS NOT NULL;

    Are all of your summary levels going to be nested like that?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 24, 2010 at 2:21 pm

    #1211289

    If you can get your developer to put each summary_level as a child of the root, you can utilize this (ideally, the different summary levels would be just "SUMMARY_LEVEL", and use the "LEVEL" to specify what the level is):

    declare @test-2 xml = '

    <ROOT>

    <SUMMARY_LEVEL_1>

    <DEPTID>42</DEPTID>

    <LEVEL>1</LEVEL>

    <DETAIL_LEVEL_1>

    <EMPID>1</EMPID>

    <NAME>Dude</NAME>

    </DETAIL_LEVEL_1>

    <DETAIL_LEVEL_1>

    <EMPID>2</EMPID>

    <NAME>NotDude</NAME>

    </DETAIL_LEVEL_1>

    </SUMMARY_LEVEL_1>

    <SUMMARY_LEVEL_2>

    <DEPTID>43</DEPTID>

    <LEVEL>2</LEVEL>

    <DETAIL_LEVEL_2>

    <EMPID>3</EMPID>

    <NAME>Duddette</NAME>

    </DETAIL_LEVEL_2>

    </SUMMARY_LEVEL_2>

    </ROOT>';

    select [DeptID] = Summary.Data.value('../DEPTID[1]','integer'),

    [Level] = summary.data.value('../LEVEL[1]','integer'),

    [EmpID] = summary.data.value('EMPID[1]','integer'),

    [Name] = summary.data.value('NAME[1]','varchar(50)')

    FROM @test.nodes('/ROOT/*/*') AS Summary(Data)

    WHERE summary.data.value('EMPID[1]','integer') IS NOT NULL

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 24, 2010 at 7:21 pm

    #1211403

    Sorry guys, I guess I wasn't clear enough. I need the XML query within Reporting Services, not a regular XML XQuery, the one that is part of defining a data set.

    I think I will work on getting the developer to change the structure though. I can group the data more effectively than trying to put together a really funky query within Reporting Services, which I'm still getting acquainted with anyway.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 24, 2010 at 8:12 pm

    #1211411

    Grant Fritchey (8/24/2010)

    I need the XML query within Reporting Services, not a regular XML XQuery, the one that is part of defining a data set.

    Sorry to bug you with a "stupid" question, but does it absolutely have to be XML?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 25, 2010 at 5:31 am

    #1211545

    Jeff Moden (8/24/2010)

    Grant Fritchey (8/24/2010)

    I need the XML query within Reporting Services, not a regular XML XQuery, the one that is part of defining a data set.

    Sorry to bug you with a "stupid" question, but does it absolutely have to be XML?

    Not a stupid question at all.

    Yes, it does. We've got a paranoid group of PeopleSoft users that are screening everything through a web service. The web service is my only contact point with the data. So it's coming back as XML.

    It works great, but you have to use odd query syntax in Reporting Services. It works through XML. For example, from the Books Online:

    <Query>

    <Method Name="ListChildren"

    Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"/>

    </Query>

    I'm, slowly, hacking my way through how it works. I was hoping for a shortcut from the brains around here.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 25, 2010 at 12:00 pm

    #1211827

    Grant, I'd really like to see what you end up with on this. For one, I don't understand how you're running this query in RS.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 25, 2010 at 12:15 pm

    #1211838

    Yeah, this might be worth a blog post, there's not much out there.

    In a nutshell, I'm using XML as the data source. So the type of data source is xml and the connection string is: http://localhost:31020/WebSite1/sample-data.xml

    That's just a local source where I stored the file as part of this test.

    So there's actually no query running to retrieve the data. Instead the XML schema is being examined by RS and a structure is created based on the first repeating pattern. Otherwise I have to use XQuery-like syntax to delve into other parts of the structure. Here's a document on it.

    But, I went back to the developers and got them to flatten out the structure so that each level is stored as one element and the details are stored as elements within the level. Works much better.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 3, 2010 at 6:55 am

    #1216128

    After the silly work I had to go through to get this to function, I went ahead and made it into a blog post[/url] if anyone is interested.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 3, 2010 at 7:46 am

    #1216167

    Grant Fritchey (9/3/2010)

    After the silly work I had to go through to get this to function, I went ahead and made it into a blog post[/url] if anyone is interested.

    The blog summarizes it nicely - well done Grant.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 3, 2010 at 7:48 am

    #1216170

    Thanks.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply