Inserting XML using 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)

Inserting XML using OPENXML

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-15 : 18:18:30

How can I select the XML element value by checking the attribute value.

If the attribute is "att" then get the element value.

The following throws an error.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<first att="att">
<second>att</second>
</first>
<first att="dtt">
<second>dtt</second>
</first>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID varchar(10) '/ROOT/sai/@att=att/second',
CustomerID varchar(10) '/ROOT/sai/@att=dtt/second'
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-15 : 18:34:20

See
http://weblogs.sqlteam.com/peterl/archive/2008/03/26/Some-XML-search-approaches.aspx
http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 01:16:37

do you mean this?


DECLARE @idoc int
DECLARE @doc xml
SET @doc ='
<ROOT>
<first att="att">
<second>att</second>
</first>
<first att="dtt">
<second>dtt</second>
</first>
<first att="we">
<second>we</second>
</first>
<first att="att">
<second>att2</second>
</first>
</ROOT>'

SELECT t.u.value('./second[1]','varchar(10)') as [second],
t.u.value('.','varchar(10)') as [first]
FROM @doc.nodes('/ROOT/first[@att="att"]') t(au)

output
----------------------------
second     first
---------- ----------
att        att
att2       att2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-16 : 12:05:19

Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources