Extracting data with OPENXML - 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)

Extracting data with OPENXML

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2011-03-23 : 20:39:28

Hi all,

I need to create a SP which should accept 1 to 20 parameters. Searching around it seems that the best way is to supply the parameters as XML-File to MS SQL then, right? But then I could not find an example how to retrieve the text of the nodes, even in SQL Books Online it's all about the attributes only.

So my XML actually looks like this:


<option class="selected" selected="selected" value="123">Human Resources</option>
<option class="selected" selected="selected" value="124">Controlling</option>
<option class="selected" selected="selected" value="-1">Sales</option>
<option class="selected" selected="selected" value="125">Facility</option>

My SQL SP should now do the following:

If the 'value' is > 0 it should return from Table 'Cost Center' all details where CostCenterID = value

If the 'value' is -1 then it should return from Table 'Cost Center' all details where CostCenterName = (selected text, e.g. 'Sales')

Is XML the right choice for this request? And how could I then access the text and the value.

Many thanks in advance!

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2011-03-24 : 13:32:46

Now i found another solution. It's not with OpenXML but with XQuery and works as expected.


DECLARE @XMLVal VARCHAR(500)
SET @XMLVal  = '<root><option class="selected" selected="selected" value="123">Human Resources</option><option class="selected" selected="selected" value="124">Controlling</option><option class="selected" selected="selected" value="-1">Sales</option><option class="selected" selected="selected" value="125">Facility</option></root>'

  DECLARE @X xml  
SET @X = CAST(@xmlVal as xml) 
SELECT Y.ID.value('data(@value)', 'int') as ValueID
         FROM @X.nodes('/root/option') as Y(ID)
		where Y.ID.value('data(@value)', 'int') > 0

SELECT Y.ID.value('data(.)', 'varchar(max)') as ValueID
         FROM @X.nodes('/root/option') as Y(ID)
		where Y.ID.value('data(@value)', 'int') < 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources