T-SQL从字段中读取配置的xml数据

DECLARE @Persons int;
DECLARE @PersonsXML varchar(max);
SELECT @PersonsXML = '<?xml version=''1.0'' encoding=''gb2312''?>
<persons>
    <person>
        <no>1</no>
        <name>张三</name>
        <sex>男</sex>
        <tel>025-12341234</tel>
        <birthDate>1999-8-8</birthDate>
    </person>
    <person>
        <no>2</no>
        <name>李四</name>
        <sex>男</sex>
        <tel>025-12341234</tel>
        <birthDate>1999-8-8</birthDate>
    </person>
    <person>
        <no>3</no>
        <name>王二麻子</name>
        <sex>男</sex>
        <tel>025-12341234</tel>
        <birthDate>1999-8-8</birthDate>
    </person>
</persons>';
EXEC sp_xml_preparedocument @Persons output,@PersonsXML;
select * from openxml(@Persons,'persons/person',2)
with (no int,name varchar(64),sex VARCHAR(4),tel varchar(32),birthDate varchar(32));
EXEC sp_xml_removedocument @Persons;

参考：

1、docs.microsoft.com/en-us/sql/r…

2、docs.microsoft.com/en-us/sql/t…