[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Extracting XML value

Posted on 2014-03-04
4
Medium Priority
?
282 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 41

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 41

Accepted Solution

by:
Kyle Abrahams earned 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

649 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