T-SQL Retrieving Data from XML Nodes

Question

Post reply

T-SQL Retrieving Data from XML Nodes

WardyWonderland

Right there with Babe

Points: 759

May 15, 2019 at 2:28 pm

#3642392

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

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

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: 76948 More actions · Answer 2

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]);

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

WardyWonderland Right there with Babe Points: 759 More actions · Answer 3

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: 5296 More actions · Answer 4

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 4 years, 12 months ago by andycadley.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

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?