Solved

SQL XML

Posted on 2013-06-10
6
449 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Independent Software Vendors: 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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 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