SQL - Casting to xml datatype without using a temporary table?

Question

I'm writing some reporting stored procedures, and need to parse some XML in SQL 2008. It's for the Umbraco CMS system, which stores its versioned data in a peculiar way that's not suited to querying many columns from. It does also keep an XML cache of all published items, which seems more suited for querying.

The XML cache is stored as an NText data type, and I've been able to parse it with SQL's XML library well enough. My question is - Is it possible to cast several XML rows to the XML data type, without using the temporary table?

The example SP is below - which utilizes a temporary table. Extra points for any pointers on being more efficient with retrieving the values.

I query the node several times, so assume that casting it each time would be inefficient.

-- Create temporary table that uses the XML data type
create table #XmlTable (
NodeId int,
NodeXml xml
)

-- Move all XML for documents of type '1121' into there
insert into #XmlTable(NodeId, NodeXml)
select cx.* from cmsContentXml cx
join cmsContent cc on cc.nodeId = cx.nodeId
where cc.contentType = 1121

select un.text [Document Name], 
xt.NodeXml.query('//node/data [@alias=''fieldOne'']').value('.', 'nvarchar(max)') [Field One],
xt.NodeXml.query('//node/data [@alias=''fieldTwo'']').value('.', 'nvarchar(max)') [Field Two],
xt.NodeXml.query('//node/data [@alias=''fieldThree'']').value('.', 'nvarchar(max)') [Field Three]

 from #XmlTable xt
 join umbracoNode un on un.id = xt.NodeId

-- Drop temporary table when finished
drop table #XmlTable

Each XML node looks approximately like:

<node id="111">
  <data alias='fieldOne'>Value 1</data>
  <data alias='fieldTwo'>Value 2</data>
  <data alias='fieldThree'>Value 3</data>
  <data alias='fieldFour'>Value 4</data>
  <data alias='fieldFive'>Value 5</data>
</node>

Thanks in advance.

There's nothing wrong or slow about temporary tables. I wouldn't rewrite it just to avoid temporary tables. — Andomar
– Andomar, Commented Jan 27, 2011 at 9:41

RichardTheKiwi · Accepted Answer · 2011-01-27 11:40:19Z

Here's a working sample. Just inline it as a subquery and perform the double nvarchar->xml conversion.

create table cmsContentXml(nodeid int, nodexml ntext)
insert cmsContentXml
select c, '
    <node id="' + right(c, 10)+ '">
      <data alias=''fieldOne''>Value ' + right(c * 100 + 1, 10)+ '</data>
      <data alias=''fieldTwo''>Value ' + right(c * 100 + 2, 10)+ '</data>
      <data alias=''fieldThree''>Value ' + right(c * 100 + 3, 10)+ '</data>
      <data alias=''fieldFour''>Value ' + right(c * 100 + 4, 10)+ '</data>
      <data alias=''fieldFive''>Value ' + right(c * 100 + 5, 10)+ '</data>
    </node>'
from (values(1),(2),(3),(4),(5)) t(c)

create table umbracoNode(id int, text varchar(100))
insert umbracoNode values (1, 'test1'), (2, 'test2'), (1, 'test3')

select un.text [Document Name], 
xt.NodeXml.query('//node/data [@alias=''fieldOne'']').value('.', 'nvarchar(max)') [Field One],
xt.NodeXml.query('//node/data [@alias=''fieldTwo'']').value('.', 'nvarchar(max)') [Field Two],
xt.NodeXml.query('//node/data [@alias=''fieldThree'']').value('.', 'nvarchar(max)') [Field Three]
from (
    select cx.nodeid, convert(xml,convert(nvarchar(max),cx.nodexml)) nodexml
    from cmsContentXml cx
    ) xt join umbracoNode un on un.id = xt.NodeId

Note: I dropped one table from the inner join since it was not necessary to illustrate this.

Output

Document Name   Field One   Field Two   Field Three
test1           Value 101   Value 102   Value 103
test3           Value 101   Value 102   Value 103
test2           Value 201   Value 202   Value 203

RobinG · Accepted Answer · 2011-01-27 09:52:16Z

1

Switching to table variables could improve your performance as:

table variables aren't logged (don't participate in the transaction)
SP's with temp tables can't be pre-compiled, but those with table variables can be
BUT, if there are a large number of rows, temp tables will be much faster, as you can index them

answered Jan 27, 2011 at 9:52

RobinG

1966 bronze badges

Collectives™ on Stack Overflow

SQL - Casting to xml datatype without using a temporary table?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related