SQL Server ->> XML方法

1. 得到XML类型中某个节点下子节点的数量

DECLARE @xml xml
SET @xml = '
<Parameters>
    <Parameter name = "param_executionid" type="Int64" />
    <Parameter name = "param_loadfromsourceSP" type="String" />
    <Parameter name = "param_loadtoStageTableSP" type="String" />
    <Parameter name = "param_objectname" type="String" />
</Parameters>';


SELECT @xml.value('count(/Parameters/Parameter)', 'int')

 

2. 把XML转成Table

DECLARE @xml xml
SET @xml = '
<Parameters>
    <Parameter name = "param_executionid" type="Int64" />
    <Parameter name = "param_loadfromsourceSP" type="String" />
    <Parameter name = "param_loadtoStageTableSP" type="String" />
    <Parameter name = "param_objectname" type="String" />
</Parameters>';


SELECT T.c.query('.').value('(//@name)[1]', 'varchar(50)') AS A,
    T.c.query('.').value('(//@type)[1]', 'varchar(50)') AS B
FROM @xml.nodes('/Parameters/Parameter') T(c)

 3. 把结果集转成XML

SELECT parameter_name name, data_type type, ISNULL(default_value,'') value
FROM SSISDB.catalog.object_parameters par 
WHERE object_name = 'xxxxx'
ORDER BY par.parameter_id 
FOR XML RAW ('Parameter'), ROOT ('Parameters')

 

参考：

Manipulating XML Data in SQL Server

OPENXML (Transact-SQL)

sp_xml_preparedocument (Transact-SQL)

SELECT an XML string to a table – SQL Server

Using the FOR XML Clause to Return Query Results as XML

insert (XML DML)

replace value of (XML DML)

query() Method (xml Data Type)

Examples of using XQuery to update XML Data in SQL Server

sql:column() Function (XQuery)

sql:variable() Function (XQuery)