Solved

SQL XML

Posted on 2013-06-10
6
448 Views
Last Modified: 2013-06-10
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
Comment
Question by:dastaub
[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
  • 3
  • 2
6 Comments
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 250 total points
ID: 39235851
--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
 

Author Comment

by:dastaub
ID: 39236113
what does the [1] indicate?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39236426
[1] indicates the first occurrence of that node (as contained in the snippet you provided)
0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39236447
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
 

Author Comment

by:dastaub
ID: 39236615
-- 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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39236633
thanks - glad you got what you needed. Cheers, Paul
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

762 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