Solved

Extracting XML value

Posted on 2014-03-04
4
277 Views
Last Modified: 2014-03-06
SQL 2008

I have the following example of xml in a sql column in a table

<CaseInception xmlns="http://www.smith.com/schemas/catch/bft" CaseTarget="C3">
  <Basic>
    <Client>
      <Source>001-001-34</Source>
           </Client>

I want to extract the <Soucrce> value from this (in this case) 001-001-34 from the xml

How would I do this?
0
Comment
Question by:halifaxman
[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
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39903492
this should do:
declare @xml xml 
set @xml = '<CaseInception xmlns="http://www.smith.com/schemas/catch/bft" CaseTarget="C3">
  <IM3eBasic>
    <Client>
      <Source>001-001-34</Source>
           </Client> </IM3eBasic></CaseInception>'

;WITH XMLNAMESPACES(DEFAULT 'http://www.smith.com/schemas/catch/bft')
select x.value('.', 'varchar(100)') v
from @xml.nodes('//Source') as T(X)
 --, @xml.value('//Source/*', 'varchar(100)')

Open in new window

0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39903538
;with XMLNAMESPACES (
 'http://www.smith.com/schemas/catch/bft' as ms
  )

select
   c.query('data(./ms:source)')
from
   (
    select XMLCOL from table
   ) A1 (x)
   cross apply x.nodes('/ms:CaseInception/ms:IM3eBasic/ms:Client') XMLTABLE(c)



My xpath might be off but that's the general approach.
0
 

Author Comment

by:halifaxman
ID: 39904151
Thanks last answer worked

However I want to join other tables to it as below, and want to able to select matterid as below I keep getting the error 'A1' has more columns than were specified in the column list. Any ideas.

Thanks for your help so far

;
WITH XMLNAMESPACES (
 'http://www.jewel.com/schemas/FlexiWeb/CIF' AS ms
  )

SELECT c.matterid,
   CAST(c.query('data(./ms:SourceOfBusiness)') AS VARCHAR)  [ws]
FROM
   (
    SELECT j.Manifest,m.matterid FROM matter m INNER JOIN dbo.WorkSource ws ON ws.WorkSourceId = m.WorkSourceId
LEFT OUTER JOIN [FlexiWebII].[dbo].[Promotion] p ON m.AccountsReference = p.PromotionValue
LEFT OUTER JOIN [FlexiWebII].[dbo].[Job] j ON j.JobId = p.JobId
WHERE ws.WorkTypeId = 17
   ) A1 (x)
   CROSS APPLY x.nodes('/ms:CaseInception/ms:IM3eBasic/ms:Client') XMLTABLE(c)
0
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 39904165
;
WITH XMLNAMESPACES (
 'http://www.jewel.com/schemas/FlexiWeb/CIF' AS ms
  )

SELECT c.matterid,
   CAST(c.query('data(./ms:SourceOfBusiness)') AS VARCHAR)  [ws]
FROM
   (
    SELECT j.Manifest,m.matterid FROM matter m INNER JOIN dbo.WorkSource ws ON ws.WorkSourceId = m.WorkSourceId
LEFT OUTER JOIN [FlexiWebII].[dbo].[Promotion] p ON m.AccountsReference = p.PromotionValue
LEFT OUTER JOIN [FlexiWebII].[dbo].[Job] j ON j.JobId = p.JobId
WHERE ws.WorkTypeId = 17
   ) A1 (x, MatterID)
   CROSS APPLY x.nodes('/ms:CaseInception/ms:IM3eBasic/ms:Client') XMLTABLE(c)
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

691 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