Solved

xml data type versus OPENXML

Posted on 2009-07-09
8
617 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
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.

 

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

862 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

23 Experts available now in Live!

Get 1:1 Help Now