[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

xml data type versus OPENXML

Posted on 2009-07-09
8
Medium Priority
?
644 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
[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
  • 4
  • 2
  • 2
8 Comments
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 100 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 700 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

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.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

650 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