T-SQL Retrieving Data from XML Nodes

  • WardyWonderland

    Say Hey Kid

    Points: 699

    Hi all,

    Been going mad most of today with trying to retrieve CoverType from the below XML data:

    If anyone could point me in the right direction it would be much appreciated!

    Thanks,

    select [ClvInc].[Tx].[value]('/salestermsoutputs[1]/policy[1]/CoverType[1]', 'nvarchar(100)')  AS x
    ,quote
    FROM TEMPDYNAMICPRICINGTEST X
    outer Apply Quote.nodes('ComponentEvent/EventPayload/AggregatorEventPayload/Policy/Versions/PolicyVersion/RiskItems/RiskItem/SalesTermsDetails') [ClvInc]([Tx]);

    XML

  • Matt Miller (4)

    SSC Guru

    Points: 124150

    Try this:

    select [Tx].[value]('(salestermsoutputs[1]/policy[1]/CoverType)[1]', 'nvarchar(100)')  AS x
    ,quote
    FROM TEMPDYNAMICPRICINGTEST X
    outer Apply Quote.nodes('ComponentEvent/EventPayload/AggregatorEventPayload/Policy/Versions/PolicyVersion/RiskItems/RiskItem/SalesTermsDetails') [ClvInc]([Tx]);

     

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • drew.allen

    SSC Guru

    Points: 76413

    WardyWonderland wrote:

    Hi all, Been going mad most of today with trying to retrieve CoverType from the below XML data: If anyone could point me in the right direction it would be much appreciated! Thanks,

    select [ClvInc].[Tx].[value]('/salestermsoutputs[1]/policy[1]/CoverType[1]', 'nvarchar(100)')  AS x
    ,quote
    FROM TEMPDYNAMICPRICINGTEST X
    outer Apply Quote.nodes('ComponentEvent/EventPayload/AggregatorEventPayload/Policy/Versions/PolicyVersion/RiskItems/RiskItem/SalesTermsDetails') [ClvInc]([Tx]);

    XML

    You can have two different types of paths in XQuery: absolute and relative.  Absolute paths start with a '/' and indicate that you should start at the top of the document; relative paths do not start with a '/' and indicate that you should start at the current node.

    It looks like you have switched the two types of paths.  You are using an absolute path in your .value() function when you should be using a relative path, and you are using a relative path in your .node() function when you should probably be using an absolute path.

    There is also a shortcut you can use if you are always taking the first node at any level.  You only have to specify the index for the entire path, and not for every single node.

    The final code should look like this.

    select [ClvInc].[Tx].[value]('(salestermsoutputs/policy/CoverType)[1]', 'nvarchar(100)')  AS x
    ,quote
    FROM TEMPDYNAMICPRICINGTEST X
    outer Apply Quote.nodes('/ComponentEvent/EventPayload/AggregatorEventPayload/Policy/Versions/PolicyVersion/RiskItems/RiskItem/SalesTermsDetails') [ClvInc]([Tx]);

    Also note that XML is case sensitive.  Make sure that all of your nodes have the correct case.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA


    How to post data/code on a forum to get the best help[/url].How to Post Performance Problems[/url]

  • WardyWonderland

    Say Hey Kid

    Points: 699

    Thanks for your help guys. Unfortunately I still can't get it to work. I've checked the case and all good there.

  • andycadley

    SSCertifiable

    Points: 5261

    Sometimes I find it helps to add something like:

    [ClvInc].[Tx].query('.')

    to the selected column list. It will output the XML your nodes() function has taken you to and can help diagnose if you've got yourself to the "right" bit of the tree.

    • This reply was modified 1 week, 2 days ago by  andycadley.
  • Matt Miller (4)

    SSC Guru

    Points: 124150

    I think andy 's solution might help.  One thing I just noticed is that you have a namespace definition on the not you're triny to use as your anchor, which can make find it a bit more tricky.  A simple adjustment might help:

    select [Tx].[value]('(*:salestermsoutputs[1]/policy[1]/CoverType)[1]', 'nvarchar(100)')  AS x
    ,quote
    FROM TEMPDYNAMICPRICINGTEST X
    outer Apply Quote.nodes('ComponentEvent/EventPayload/AggregatorEventPayload/Policy/Versions/PolicyVersion/RiskItems/RiskItem/SalesTermsDetails') [ClvInc]([Tx]);

    Just adding the *: notation can in some cases get around that.  Otherwise you may need to go through the WITH XMLNAMESPACES() syntax to do so.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 6 posts - 1 through 6 (of 6 total)

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