XML/ SQL Server joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

XML/ SQL Server joins

Author

Topic

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-09-04 : 11:04:49

I have a table containing XML snippets sorta like this:

rule_id (int), definition (xml)
1, '<rule name="value1 exists"><value1>5</value1></rule>'
2, '<rule name="value1 exists"><value1>6</value1></rule>'
3, '<rule name="value1 exists"><value1>7</value1></rule>'
4, '<rule name="value2 exists"><value2>4.5</value2></rule>'
5, '<rule name="value2 exists"><value2>5.5</value2></rule>'
6, '<rule name="value2 exists"><value2>6.5</value2></rule>'

The rule node will always exist at the top, but depending on the complexity of the rule, the node schema underneath may look wildly different. Ultimately, I have been tasked to serialize this table as an XML "rule set", so I need to generate an XML document looking like this:


<rules>
 <rule name="value1 exists">
  <value1>5</value1>
  <value1>6</value1>
  <value1>7</value1>
 </rule>
 <rule name="value2 exists">
  <value2>4.5</value2>
  <value2>5.5</value2>
  <value2>6.5</value2>
 </rule>
</rules>

It seems to me there should be an elegant solution for this with CROSS APPLY, but frankly my grasp on XQuery stuff is limited. I do not need to necessarily get the final output in one query, though... I'll be perfectly happy with a reliable and relatively zippy query that will get my data in table format looking like this:

rule_name (varchar(50)), InnerXML(nvarchar(max))
'value1 exists', '<value1>5</value1>'
'value1 exists', '<value1>6</value1>'
'value1 exists', '<value1>7</value1>'
'value2 exists', '<value2>4.5</value2>'
'value2 exists', '<value2>5.5</value2>'
'value2 exists', '<value2>6.5</value2>'

Any input is greatly appreciated.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-09-04 : 13:31:21

OK, here's the answer:


SELECT 
   R.n.value('@name', 'varchar(50)') as rule_name,
   CAST(t.[definition].query('//rule/*') AS NVARCHAR(MAX) as inner_xml
FROM
   rule_table t
CROSS APPLY
   t.[definition].nodes('//rule') R(n);

This will give the table format I was looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources