Read xml data in stored procedure in SQL Server

Question

I have this T-SQL script:

DECLARE @idoc int

DECLARE @doc nvarchar(200)
SET @doc ='<ArrayOfString>
            <string>AL</string>
            <string>DZ</string>
        </ArrayOfString>'

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

    select *  
    FROM OPENXML (@idoc, '/ArrayOfString',2)
    WITH (string varchar(50))
    EXEC sp_xml_removedocument @idoc

This is a stored procedure. I send to it some serialized xml (in this variant I declare it as hard code).

I want to get all <string> element's values. In this variant it must be: AL and DZ, but I get only 'AL'. What is it incorrect in my script?

somesh · Accepted Answer · 2013-12-04 09:00:56Z

5

declare @xml xml
set @xml = '<ArrayOfString 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <string>Dev_1</string>
    <string>Dev_3</string>
</ArrayOfString>'

SELECT 
    T.C.value('.', 'VARCHAR(10)')
from @xml.nodes('/ArrayOfString/string') T(C)

answered Dec 4, 2013 at 9:00

somesh

3,5784 gold badges19 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

IFrizy Over a year ago

Thanks.It's variant work good! But, What is 'T.C.value' and T(C) in your code?

somesh Over a year ago

"T(C)" is alias for "@xml.nodes('/ArrayOfString/string')" in the last statement.

IFrizy Over a year ago

Good! But if it is alias, why we write 'T.C.value'? Sorry, if its silly question! :)

somesh Over a year ago

It is used fetch the value or content from XML node.

Ocaso Protal · Accepted Answer · 2013-12-04 09:18:18Z

3

If you want to use OPENXML try this:

DECLARE @idoc int

DECLARE @doc nvarchar(200)
SET @doc ='<ArrayOfString>
            <string>AL</string>
            <string>DZ</string>
        </ArrayOfString>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

select *  
    FROM OPENXML (@idoc, '/ArrayOfString/string',2)
    WITH (string varchar(50) '.')

EXEC sp_xml_removedocument @idoc

answered Dec 4, 2013 at 9:18

Ocaso Protal

20.4k8 gold badges80 silver badges92 bronze badges

Collectives™ on Stack Overflow

Read xml data in stored procedure in SQL Server

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related