Solved

xml data type versus OPENXML

Posted on 2009-07-09
8
616 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 46
Complex SQL 10 34
VB6 - Convert HH:MM into Decimal 8 38
SQL query to summarize items per month 5 28
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now