Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

SQL XML

The below T-SQL does work.  How can the below T-SQL be altered to work with the scenario of the contents of the variable @XML1 being in an XML column and you want the results of the SELECT statement to populate columns in the same table?  

declare @xml1 xml

SET @xml1 =
'<EDMed>
  <Desc>Tylenol</Desc>
  <Dose>500</Dose>
  <Unit>tablet(s)</Unit>
  <TimeGiven>05/09/2012 11:07</TimeGiven>
 </EDMed>'

SELECT  
       Tbl.Col.value('Desc[1]', 'varchar(15)') as [Description],  
       Tbl.Col.value('Dose[1]', 'varchar(15)') as [Dose],  
       Tbl.Col.value('Unit[1]', 'varchar(15)') as [Unit],
       Tbl.Col.value('TimeGiven[1]', 'varchar(20)') as [Time Given]
FROM   @xml1.nodes('//EDMed') Tbl(Col)
0
dastaub
Asked:
dastaub
  • 3
  • 2
2 Solutions
 
Christopher GordonSenior Developer AnalystCommented:
--paste into management studio for example...

declare @xml1 xml                  --xml example 1
declare @xml2 xml                  --xml example 2

--sample table
declare @myXMLTable table (Id int identity(1,1), myXML xml)

--populate sample 1
SET @xml1 =
'<EDMed>
  <Desc>Tylenol</Desc>
  <Dose>500</Dose>
  <Unit>tablet(s)</Unit>
  <TimeGiven>05/09/2012 11:07</TimeGiven>
 </EDMed>'

--poulate sample 2
set @xml2 =  '<EDMed>
  <Desc>Chicken Nuggets</Desc>
  <Dose>1</Dose>
  <Unit>dozen(s)</Unit>
  <TimeGiven>05/01/2012 11:07</TimeGiven>
 </EDMed>'
 
insert into @myXMLTable
values (@xml1), (@xml2)
 

--t-sql to parse xml in field

SELECT  Id,
       myXML.value('(/EDMed/Desc)[1]', 'varchar(15)') as [Description],  
       myXML.value('(/EDMed/Dose)[1]', 'varchar(15)') as [Dose],  
       myXML.value('(/EDMed/Unit)[1]', 'varchar(15)') as [Unit],
       myXML.value('(/EDMed/TimeGiven)[1]', 'varchar(20)') as [Time Given]
       
FROM   @myXMLTable
0
 
dastaubAuthor Commented:
what does the [1] indicate?
0
 
PortletPaulfreelancerCommented:
[1] indicates the first occurrence of that node (as contained in the snippet you provided)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
PortletPaulfreelancerCommented:
with the requested update statement:
CREATE TABLE YourTable 
	(
     id int identity primary key, 
     description nvarchar(15), 
     dose nvarchar(15), 
     unit nvarchar(15), 
     timegiven nvarchar(20), 
     details xml
    );
INSERT INTO YourTable
(details)
values
('<EDMed>
  <Desc>Tylenol</Desc>
  <Dose>500</Dose>
  <Unit>tablet(s)</Unit>
  <TimeGiven>05/09/2012 11:07</TimeGiven>
 </EDMed>'
)

select
*
from yourtable
;

SELECT  
       details.value('(/EDMed/Desc)[1]', 'varchar(15)') as [Description],  
       details.value('(/EDMed/Dose)[1]', 'varchar(15)') as [Dose],  
       details.value('(/EDMed/Unit)[1]', 'varchar(15)') as [Unit],
       details.value('(/EDMed/TimeGiven)[1]', 'varchar(20)') as [Time Given]
FROM   yourtable
;

update yourtable
set
     description = details.value('(/EDMed/Desc)[1]', 'varchar(15)')
   , dose        = details.value('(/EDMed/Dose)[1]', 'varchar(15)')
   , unit        = details.value('(/EDMed/Unit)[1]', 'varchar(15)')
   , timegiven   = details.value('(/EDMed/TimeGiven)[1]', 'varchar(20)')
;


select
*
from yourtable
;

Open in new window

see it working at: http://sqlfiddle.com/#!3/4cdff/1
0
 
dastaubAuthor Commented:
-- Below is the solution that worked for me.  Thank You.

CREATE TABLE [dbo].[#Temp03a](  
      [ResponseXML] [xml] NOT NULL DEFAULT (''),
      [Description] [varchar](100) NOT NULL DEFAULT (''),
      [Dosage] [varchar](50) NOT NULL DEFAULT (''),
      [Unit] [varchar](50) NOT NULL DEFAULT (''),
      [TimeGiven] [varchar](25) NOT NULL DEFAULT (''),
         [TimeStopped] [varchar](25) NOT NULL DEFAULT ('')
) ON [PRIMARY]

update #Temp03a set [Description] = ResponseXML.value('(/EDMed/Desc)[1]', 'varchar(100)'),
Dosage = ResponseXML.value('(/EDMed/Dose)[1]', 'varchar(50)') ,
Unit= ResponseXML.value('(/EDMed/Unit)[1]', 'varchar(50)') ,
[TimeGiven] = ResponseXML.value('(/EDMed/TimeGiven)[1]', 'varchar(50)') ,
[TimeStopped] = ResponseXML.value('(/EDMed/TimeStopped)[1]', 'varchar(50)')
0
 
PortletPaulfreelancerCommented:
thanks - glad you got what you needed. Cheers, Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now