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
Solved

Extracting XML value

Posted on 2014-03-04
4
274 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
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

790 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