Solved

Extracting XML value

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

863 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

20 Experts available now in Live!

Get 1:1 Help Now