Plazi Plazi - 4 months ago 16
SQL Question

openxml with asynchron structure

I am working with MS SQL Server 2012.

I am trying to use the function openxml with the following statement / xml data:

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SET @SQL = '<master>
<List>
<Col>
<DisplayFieldName>Peter</DisplayFieldName>
<Value>
<string>Yes</string>
</Value>
</Col>
<Col>
<DisplayFieldName>Tom</DisplayFieldName>
<Value>
<string>No</string>
</Value>
</Col>
<Col>
<DisplayFieldName>Numerics</DisplayFieldName>
<Value>
<string>50 </string>
<string>100 </string>
<string>150 </string>
<string>200 </string>
</Value>
</Col>
</List>
</master>'


SELECT @XML = CONVERT(XML,@SQL)

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT *
FROM OPENXML(@hDoc, '/master/List/Col/Value',3)
WITH
(
string [varchar](max)
)

EXEC sp_xml_removedocument @hDoc


The result is this:

string
1 Yes
2 No
3 50


I understand why this happens, but actually I would like to display just all numerical values (50,100,150,200) or at least all values in the field (Yes, No, 50, 100, 150, 200) which also would be sufficient.

Answer

You can give this a try, might return more than what you are expecting:

SELECT *
FROM OPENXML(@hDoc, '/master/List/Col/Value/*', 3) 
WITH 
(
string [varchar](max) '.'
)