Solved

Extract Value From XML Field in TSQL For SQL Server 10.50.4033.0

Posted on 2015-01-19
6
382 Views
Last Modified: 2015-01-19
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
Comment
Question by:raynera
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40557418
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
 

Author Comment

by:raynera
ID: 40557442
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
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40557447
then you can try something like convert(xml, citBodyXML)
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40557453
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
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 40557454
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
 

Author Comment

by:raynera
ID: 40557477
Thanks very much - figured it out from that :)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question