TSQL - 查询XML和合并根节点
问题描述:
我试图建立在以下格式的XML(我们的供应商有没有XSD):TSQL - 查询XML和合并根节点
<Submission>
<Office Account="ACCOUNT">
<schedule submissionPeriod="" areaOfLaw="" scheduleNum="abc">
<outcome matterType="A:B">
<outcomeItem Name="CASE_START_DATE">DATA</outcomeItem>
<outcomeItem Name="CASE_ID">DATA</outcomeItem>
</outcome>
<outcome matterType="C:D">
<outcomeItem Name="CASE_START_DATE">DATA</outcomeItem>
<outcomeItem Name="CASE_ID">DATA</outcomeItem>
</outcome>
</schedule>
<schedule submissionPeriod="" areaOfLaw="" scheduleNum="def">
<outcome matterType="E:F">
<outcomeItem Name="CASE_START_DATE">DATA</outcomeItem>
<outcomeItem Name="CASE_ID">DATA</outcomeItem>
</outcome>
</schedule>
</Office>
<Office Account="ACCOUNT2">
<schedule submissionPeriod="" areaOfLaw="" scheduleNum="ghi">
<outcome matterType="A:B">
<outcomeItem Name="CASE_START_DATE">DATA</outcomeItem>
<outcomeItem Name="CASE_ID">DATA</outcomeItem>
</outcome>
</schedule>
</Office>
我有以下SQL
SELECT supplier_no AS 'Office/@Account' ,
UPPER(LEFT(DATENAME(MM, GETDATE()), 3)) + '-' + DATENAME(YYYY,
GETDATE()) AS 'Office/schedule/@submissionPeriod' ,
'LEGAL HELP' AS 'Office/schedule/@areaOfLaw' ,
supplier_no + '/CIVIL' AS 'Office/schedule/@scheduleNum' ,
ClaimsTable.mattype1_code + ':' + ClaimsTable.mattype2_code AS 'Office/schedule/outcome/@matterType' ,
(SELECT * FROM (
SELECT 'CASE_START_DATE' AS 'outcomeItem/@Name', 'DATA' AS 'outcomeItem' UNION ALL
SELECT 'CASE_ID' AS 'outcomeItem/@Name', 'DATA' AS 'outcomeItem'
) OutcomeItems FOR XML PATH(''), TYPE) AS 'Office/schedule/outcome'
FROM ContractsTable
LEFT OUTER JOIN ClaimsTable ON ContractsTable.contract_no = ClaimsTable.contract_no
WHERE ClaimsTable.compno = 2
AND ClaimsTable.submitted_period = 0
FOR XML PATH('Submission') ,
TYPE
这是产生下面的XML
<Submission>
<Office Account="MHESUPPLIE">
<schedule submissionPeriod="FEB-2013" areaOfLaw="LEGAL HELP" scheduleNum="MHESUPPLIE/CIVIL">
<outcome matterType="MHNS:MOUT">
<outcomeItem Name="CASE_START_DATE">13/02/2013</outcomeItem>
<outcomeItem Name="CASE_ID">001</outcomeItem>
</outcome>
</schedule>
</Office>
</Submission>
<Submission>
<Office Account="MHESUPPLIE">
<schedule submissionPeriod="FEB-2013" areaOfLaw="LEGAL HELP" scheduleNum="MHESUPPLIE/CIVIL">
<outcome matterType="MHAS:MGUA">
<outcomeItem Name="CASE_START_DATE">14/02/2013</outcomeItem>
<outcomeItem Name="CASE_ID">001</outcomeItem>
</outcome>
</schedule>
</Office>
</Submission>
显然,输出XML有多个根节点是无效。我试图找到一种方法将结果组合在一起以实现我需要的XML类型。有没有关于SQL Server 2005+如何实现的想法?
答
我简化了你的查询。假设你的查询看起来是这样的:
SELECT STR(X) AS [office/schedule]
FROM (VALUES(1),(2)) AS CompleQuery(x)
FOR XML PATH('Submission'),TYPE;
产生:
<Submission><office><schedule> 1</schedule></office></Submission><Submission><office><schedule> 2</schedule></office></Submission>
为了得到这两个办公室标签为一个提交用这个代替:
SELECT (
SELECT STR(X) AS [office/schedule]
FROM (VALUES(1),(2)) AS CompleQuery(x)
FOR XML PATH(''),TYPE
)
FOR XML PATH('Submission'),TYPE;
因此,在改变PATH('Submission')
到PATH('')
您的查询,然后将其包裹到另一个SELECT ... FOR XML PATH('Submission'),TYPE;
结果:
<Submission><office><schedule> 1</schedule></office><office><schedule> 2</schedule></office></Submission>
如果使用FOR XML PATH(''),ROOT('Submission')'会发生什么? – 2013-02-14 16:30:06