Solved

SQL XML

Posted on 2013-06-10
6
447 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 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