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)

巴特西

SQL Server ->> XML方法

最新文章

热门文章