Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

Extract Value From XML Field in TSQL For SQL Server 10.50.4033.0

I can't find an example that I can copy from that works (or I can translate) for extracting a particular value from an XML field on a table within a SELECT statement.

I can do what I need using the example in the attached file where I explicitly declare an XML string with an example of the type of XML I am parsing but I cannot translate that to a SELECT statement that retrieves the value I need on each row of the many thousands of rows in the SQL table I'm looking at.

The attached file as I've said, shows an example of the structure of the XML inside a field on my table and I need to extract the ParticipantFunction and EndorsementParticipantFunction values from the XPath "Jv-Ins-Reinsurance/Placing/ContractSection/ContractMarket".

Can anyone help?
XML-Query-Example.sql
0
raynera
Asked:
raynera
  • 4
  • 2
1 Solution
 
Robert SchuttSoftware EngineerCommented:
Try this, fill in your table name instead of #tmpdb:
--http://www.ACORD.org/Standards/AcordMsgSvc/1
;WITH XMLNAMESPACES ('http://www.ACORD.org/standards/Jv-Ins-Reinsurance/2009-1'  AS WS)

SELECT
  Nodes.node.value('WS:ParticipantFunction[1]', 'varchar(10)')  ParticipantFunction,
  Nodes.node.value('WS:EndorsementParticipantFunction[1]', 'varchar(10)')  EndorsementParticipantFunction
FROM
#tmpdb cross apply myxml.nodes('WS:Jv-Ins-Reinsurance/WS:Placing/WS:ContractSection/WS:ContractMarket') AS Nodes(node);

Open in new window

0
 
rayneraAuthor Commented:
Tried it...but got an error...

Msg 9506, Level 16, State 1, Line 3
The XMLDT method 'nodes' can only be invoked on columns of type xml.

CommunicationItem.citCommItem is my schema.table name and citBodyXML is the field.

I made a mistake and just noticed it after getting the error above...the citBodyXML field isn't an XML field type after all - DOH!

Ran the following...

--http://www.ACORD.org/Standards/AcordMsgSvc/1
;WITH XMLNAMESPACES ('http://www.ACORD.org/standards/Jv-Ins-Reinsurance/2009-1'  AS WS)

SELECT
  Nodes.node.value('WS:ParticipantFunction[1]', 'varchar(10)')  ParticipantFunction,
  Nodes.node.value('WS:EndorsementParticipantFunction[1]', 'varchar(10)')  EndorsementParticipantFunction
FROM
CommunicationItem.citCommItem cross apply citBodyXML.nodes('WS:Jv-Ins-Reinsurance/WS:Placing/WS:ContractSection/WS:ContractMarket') AS Nodes(node);
0
 
Robert SchuttSoftware EngineerCommented:
then you can try something like convert(xml, citBodyXML)
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Robert SchuttSoftware EngineerCommented:
Hm, sorry just tried it and cast/convert don't seem to work. Maybe use a temp table? I'll try with a varchar column myself as well.
0
 
Robert SchuttSoftware EngineerCommented:
Well, this works, using a CTE (common table expression) to get the xml field:
--http://www.ACORD.org/Standards/AcordMsgSvc/1
;WITH XMLNAMESPACES ('http://www.ACORD.org/standards/Jv-Ins-Reinsurance/2009-1'  AS WS)
,tmp as (select CAST(myxml as xml) as myxml from #tmpdb)
SELECT
  Nodes.node.value('WS:ParticipantFunction[1]', 'varchar(10)')  ParticipantFunction,
  Nodes.node.value('WS:EndorsementParticipantFunction[1]', 'varchar(10)')  EndorsementParticipantFunction
FROM
tmp cross apply myxml.nodes('WS:Jv-Ins-Reinsurance/WS:Placing/WS:ContractSection/WS:ContractMarket') AS Nodes(node);

Open in new window

0
 
rayneraAuthor Commented:
Thanks very much - figured it out from that :)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now