Query related to openxml

[varshan09]

I want to store xml in a table. Let's say I have following xml:
<Root>
<Row>
<Col1>Val1</Col1>
<Col2>Val1</Col2>
</Row>
<Row>
<Col1>Val2</Col1>
<Col2>Val2</Col2>
</Row>
</Root>

Is it possible to use OpenXML to insert each <Row>...</Row> as a single row in table? In the above example I want to have 2 rows in the table.

 

[vongrunt]

If you think about storing pieces of XML (<Row>...</Row>) into table column - better don't.

Otherwise, it is possible to extract element values into flat query with OPENXML() and element-centric mapping:

declare @xml varchar(2000)
set @xml = 
'<Root>
<Row><Col1>Blah1</Col1><Col2>Foo1</Col2></Row>
<Row><Col1>Blah2</Col1><Col2>Foo2</Col2></Row>
</Root>'

declare @xmldoc int
exec sp_xml_preparedocument @xmldoc output, @xml

-- insert into targettable (somecol1, somecol2)
select Col1, Col2 
from openxml( @xmldoc, '/Root/Row', 2 )
with ( Col1 varchar(8) './Col1',
       Col2 varchar(8) './Col2' 
     )

exec sp_xml_removedocument @xmldoc

In this example explicit './Col1' and './Col2' mappings are not necessary but don't hurt.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)