xml data type versus OPENXML

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.

gringogordoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
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
RiteshShahCommented:
0
gringogordoAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Anthony PerkinsCommented:
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
gringogordoAuthor Commented:
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
Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gringogordoAuthor Commented:
Brilliant thanks to both.  I hadn't even looked at the Nodes method.  doh!
0
gringogordoAuthor Commented:
Thanks very much.  That's exactly what I was looking for.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.