SQL Server - parse xml

I need to parse the above xml such that it returns the attributes in a flat
format,
e.g. abc,123
How can this be achieved using sql 2005

  Balaji replied to Anonymous

Hi

You can use openxml function.

Regards, Balaji

  Bob replied to Anonymous

Try this:

DECLARE @xml XML

SET @xml = '<SampleXML>


SELECT
root.x.value('(Table/@value, Field/@value)[1]', 'VARCHAR(50)') AS a,
root.x.value('(Table/@value, Field/@value)[2]', 'VARCHAR(50)') AS b
FROM @xml.nodes('SampleXML/*') root(x)

OPENXML to accept both attributes and elements SQL Server Hi all, Is it possible to retreive values from XML document whether they where sent as elements or attributes, using OPENXML? For example, if someone sends me: I want to be able to read both. Possible XQuery (1) IntDoc (1) WBob (1) Preparedocument (1) Removedocument (1) I am not sure about OPENXML but XQuery can do it: DECLARE @doc XML; SET @doc = N'<Root> <Customer Name = "Foo blah1"> < / Customer> - -Load the XML document into memory. EXEC sp_xml_preparedocument @intDoc OUTPUT, @chvXMLDoc SELECT * FROM OPENXML ( @intDoc, ' / Root / Customer', 8 ) WITH ( Name VARCHAR(30) '@Name' ) SELECT * FROM OPENXML ( @intDoc, ' / Root / Customer', 8 ) WITH ( Name VARCHAR(30) 'Name' ) EXEC sp_xml_removedocument @intDoc GO HTH wBob Rate the post keywords: OPENXML, to, accept, both, attributes, and, elements description: Hi all, Is it possible to retreive values

Openxml() Edge Table - root element problem SQL Server Sql Server 2005: Requirement: return results of openxml() to an Edge Table. Function body: openxml(@handle, ' / / Charge', 2) [where 'Charge' is a parent element in the .xml doc) Question: My openxml() fails to parse an .xml doc with the root element attributes shown below (function returns -0- rows). If I add a character to the 2nd "xmlns" in the .xml doc root attributes (or if I delete a character, or replace that 2nd "xmlns" with "xmlns:AnyWord"), then the function works fine. Anybody know why the .xml doc root element attributes are causing openxml() to fail? Also, is it 'best practice' to alter the attributes so the function works

another sort of flattening problem, but more of a tree issue I think. create table attributes ( parent_id int, attributes_id int, attributes_name varchar(255) ) insert into attributes Values(Null, 1, 'Gender') insert into attributes Values(1, 2, 'Male') insert into attributes Values(1, 2, 'Female') insert into attributes values(null, 3, 'North America') insert into attributes values(3, 4, 'Canada') insert into attributes Values(3, 5, 'United States') insert into attributes values(5, 6, 'Alabama') insert into attributes values(5, 7, 'Alaska') - - query here drop table