Solved

xml data type versus OPENXML

Posted on 2009-07-09
8
623 Views
Last Modified: 2013-12-25
Hi,

I have been sending XML strings up to SQL server when I have a lot of data for quite a few years now and letting the sprocs deal with it with OPENXML.  I was wondering if I should pass it as an XML datatype now ?  In my current scenario I have an XML document of variable size (3 to 3000 records say) and I want to iterate through one of the attributes in an element 4 levels down the structure.  So far playing with the xml datatype for the first time this doesn't seem to be ther right tool for the job ?  It seems to me the query method might get me some elements and the value method is good for getting one attribute value out of the xml but if I want to do some real work on in the stored procedure I'm still better off using OPENXML.  

I guess my question is does anyone think I'm missing the point on the xml datatype and should be using this and is OPENXML supposed to be obsolete now or can I continue to use it  without being a dinosour!?

Cheers.

0
Comment
Question by:gringogordo
  • 4
  • 2
  • 2
8 Comments
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 25 total points
ID: 24812116
well I prefer XML data type than OPENXML. you can have a look at below link to know differences between these two.

http://www.exforsys.com/tutorials/sql-server-2005/using-xml-in-sql-server-2005.html

as long as obsolete concern, i don't think so it is still in a list so if you are comfortable with OPENXML, than you can choose it.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24812152
0
 

Author Comment

by:gringogordo
ID: 24812493
Thanks for that.

I'm still struggling a bit though.  I have this bit of sample XML (see below) which I will be passing from code.  As far as I can see with the XML data typwe it is useful if you have one small scrap in each row of a table but it is not versatile enough to query one solid lump of XML.  The sample below for example I want to iterate all 'Element1/Item/To/User/@email' values.  As far as I can see this is not possible and the data type is for a different purpose (maybe storing mutiple rows each with their own Item node for example).  There isn't an easy way to get a 'recordset' or iteration of

test1@test.com
test2@test.com
...

I'm more than happy to use the OPENXML in this case but I'm trying to work out if I was trying to use the xml typwe for the wrong purpose or whether I am missing out on some functionality!?

Thanks.

<Element1 Document_Name="Element1s" Document_Description="" Document_Version="1" Document_Time="18/12/2007 14:24:01" >
      <Item Kind="Element1" Title="Quarter 2">
            <From>
                  <User Name="sysadmin" Email="test@test.com" />
            </From>
              <To>
                    <User Name="Test1" Email="test1@test.com" />
              </To>
              <Item Kind="Section" Title="Title 1">
                    <Item Kind="Comparison" Title="Actual vs. Target" />
                    <Item Kind="Period" Title="2007/09" />
                   <Item Kind="Measure (Location)" Title="TitleX">
                          <Item Kind="Index" Title="--" />
                    </Item>
                   <Item Kind="Measure (Location)" Title="TitleY">
                          <Item Kind="Index" Title="--" />
                    </Item>
              </Item>
        </Item>
       <Item Kind="Element1" Title="Quarter 2 Strategies - time for completion">
             <From>
                    <User Name="sysadmin" Email="test@test.com" />
              </From>
             <To>
                    <User Name="Test2" Email="test2@test.com" />
              </To>
              <Item Kind="Section" Title="Title3">
                    <Item Kind="Comparison" Title="Actual vs. Target" />
                    <Item Kind="Period" Title="2007/09" />
                   <Item Kind="Measure (Location)" Title="Title4">
                          <Item Kind="Index" Title="--" />
                    </Item>
                   <Item Kind="Measure (Location)" Title="Title5">
                          <Item Kind="Index" Title="--" />
                    </Item>
              </Item>
        </Item>
 </Element1>
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 75

Expert Comment

by:Anthony Perkins
ID: 24815970
Since I am not following you, why not post your OPENXML solution and we can give you the alternative using Xml Data methods.

One thing to note, the Xml Data methods unlike OPENXML require that you create any stored procedures with SET QUOTED_IDENTIFIER ON
0
 

Author Comment

by:gringogordo
ID: 24821202
Okay thanks.  It's quite simple using OpenXML which is why I may be missing something.  

I've attached the code from the sproc (in reality @Alert would be a parameter).

Let me know what else would be useful...

Thanks.
DECLARE @Alert as varchar (MAX)
 
SET @Alert = '<Element1 Document_Name="Element1s" Document_Description="" Document_Version="1" Document_Time="18/12/2007 14:24:01" >
      <Item Kind="Element1" Title="Quarter 2">
            <From>
                  <User Name="sysadmin" Email="test@test.com" /> 
            </From>
              <To>
                    <User Name="Test1" Email="test1@test.com" /> 
              </To>
              <Item Kind="Section" Title="Title 1">
                    <Item Kind="Comparison" Title="Actual vs. Target" /> 
                    <Item Kind="Period" Title="2007/09" /> 
                   <Item Kind="Measure (Location)" Title="TitleX">
                          <Item Kind="Index" Title="--" /> 
                    </Item>
                   <Item Kind="Measure (Location)" Title="TitleY">
                          <Item Kind="Index" Title="--" /> 
                    </Item>
              </Item>
        </Item>
       <Item Kind="Element1" Title="Quarter 2 Strategies - time for completion">
             <From>
                    <User Name="sysadmin" Email="test@test.com" /> 
              </From>
             <To>
                    <User Name="Test2" Email="test2@test.com" /> 
              </To>
              <Item Kind="Section" Title="Title3">
                    <Item Kind="Comparison" Title="Actual vs. Target" /> 
                    <Item Kind="Period" Title="2007/09" /> 
                   <Item Kind="Measure (Location)" Title="Title4">
                          <Item Kind="Index" Title="--" /> 
                    </Item>
                   <Item Kind="Measure (Location)" Title="Title5">
                          <Item Kind="Index" Title="--" /> 
                    </Item>
              </Item>
        </Item>
 </Element1>'
 
--*/
 
DECLARE @idoc int
 
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @Alert
 
-- SELECT statement that uses the OPENXML rowset provider.
SELECT  T2EmailAddress  
FROM       OPENXML (@idoc, '/Element1/Item/Item/Item', 2) 
WITH (Title   varchar(50) '@Title') X
INNER JOIN [ExistingTable1] M
ON X.Title = M.MeasureName
INNER JOIN [ExistingTable2] T 
ON M.Table1Id = T.Table1Id 
GROUP BY T2EmailAddress  
 
EXEC sp_xml_removedocument @idoc

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 175 total points
ID: 24823693
These two give me exactly the same response:
1. Using OPENXML

DECLARE @idoc int
 
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @Alert
 
-- SELECT statement that uses the OPENXML rowset provider.
SELECT  *
FROM    OPENXML (@idoc, '/Element1/Item/Item/Item', 2) WITH (
            Title varchar(50) '@Title'
            ) X
 
EXEC sp_xml_removedocument @idoc

2. Using Xml Data Type Methods
DECLARE @AlertXml XML
SET @AlertXml = CAST(@Alert AS XML)

SELECT  X.c.value('@Title', 'varchar(50)') Title
FROM    @AlertXml.nodes('/Element1/Item/Item/Item') X(c)

Output in both cases:
Actual vs. Target
2007/09
TitleX
TitleY
Actual vs. Target
2007/09
Title4
Title5

So I have to assume that you should be able to write it as follows:

SELECT  T2EmailAddress
FROM    @AlertXml.nodes('/Element1/Item/Item/Item') X(c)
        INNER JOIN ExistingTable1 M ON X.c.value('@Title', 'varchar(50)') = M.MeasureName
        INNER JOIN ExistingTable2 T ON M.Table1Id = T.Table1Id
GROUP BY T2EmailAddress
0
 

Author Comment

by:gringogordo
ID: 24824324
Brilliant thanks to both.  I hadn't even looked at the Nodes method.  doh!
0
 

Author Closing Comment

by:gringogordo
ID: 31601506
Thanks very much.  That's exactly what I was looking for.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2014 always on 31 58
NOLOCK still valid for 2014? 5 35
sql 2016 Integration Service connecting to 2012 3 29
SQL Syntax Grouping Sum question 7 24
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

791 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