Solved

Extracting XML value

Posted on 2014-03-04
4
268 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 142

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 39

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 39

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Viewers will learn how the fundamental information of how to create a table.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now