将多行XML值和主键合并到一个SQL查询表中
问题描述:
我有一个名为VPX_EVENT_ARG的表,其中一列ARG_DATA包含XML值。将多行XML值和主键合并到一个SQL查询表中
Table 1
+----------+-------------------+----------+
| EVENT_ID | ARG_TYPE | ARG_DATA |
+----------+-------------------+----------+
| 7121001 | vim.vm.ConfigSpec | XML1 |
| 7121002 | vim.vm.ConfigSpec | XML2 |
| 7121003 | vim.vm.ConfigSpec | XML3 |
+----------+-------------------+----------+
XML1,XML2和XML3是XML值。他们太长,无法输入表格。这是真正的价值。实际上它们出现在一行中。
XML1,例如,
<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="5.5" xsi:type="VirtualMachineConfigSpec"><changeVersion>2015-09-24T10:02:53.866694Z</changeVersion><files><vmPathName>ds:///vmfs/volumes/54e5d10c-c527b7f3-7eea-a0d3c1f01404/CommVault VM Test/CommVault VM Test.vmx</vmPathName></files><deviceChange><operation>remove</operation><device xsi:type="VirtualDisk"><key>2003</key><deviceInfo><label>Hard disk 4</label><summary>20,971,520 KB </summary></deviceInfo><backing xsi:type="VirtualDiskFlatVer2BackingInfo"><fileName>ds:///vmfs/volumes/54e5d10c-c527b7f3-7eea-a0d3c1f01404/CommVault VM Test/CommVault VM Test_2.vmdk</fileName><diskMode>persistent</diskMode><split>false</split><writeThrough>false</writeThrough><thinProvisioned>false</thinProvisioned><uuid>6000C29b-e652-b5fe-76fa-18f6de988807</uuid><contentId>5bd085f0f9391346751e1e7efffffffe</contentId><digestEnabled>false</digestEnabled></backing><controllerKey>1000</controllerKey><unitNumber>3</unitNumber><capacityInKB>20971520</capacityInKB><shares><shares>1000</shares><level>normal</level></shares><storageIOAllocation><limit>-1</limit><shares><shares>1000</shares><level>normal</level></shares></storageIOAllocation></device></deviceChange></obj>
我将XML1分离成多行,这样它会更容易阅读。
<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="5.5" xsi:type="VirtualMachineConfigSpec"><changeVersion>2015-09-24T10:02:53.866694Z</changeVersion><files><vmPathName>ds:///vmfs/volumes/54e5d10c-c527b7f3-7eea-a0d3c1f01404/CommVault VM Test/CommVault VM Test.vmx</vmPathName></files>
<deviceChange>
<operation>remove</operation>
<device xsi:type="VirtualDisk">
<key>2003</key>
<deviceInfo>
<label>Hard disk 4</label>
<summary>20,971,520 KB </summary>
</deviceInfo>
<backing xsi:type="VirtualDiskFlatVer2BackingInfo">
<fileName>ds:///vmfs/volumes/54e5d10c-c527b7f3-7eea-a0d3c1f01404/CommVault VM Test/CommVault VM Test_2.vmdk
</fileName>
<diskMode>persistent</diskMode>
<split>false</split>
<writeThrough>false</writeThrough>
<thinProvisioned>false</thinProvisioned>
<uuid>6000C29b-e652-b5fe-76fa-18f6de988807</uuid>
<contentId>5bd085f0f9391346751e1e7efffffffe</contentId>
<digestEnabled>false</digestEnabled>
</backing>
<controllerKey>1000</controllerKey>
<unitNumber>3</unitNumber>
<capacityInKB>20971520</capacityInKB>
<shares><shares>1000</shares><level>normal</level></shares><storageIOAllocation><limit>-1</limit><shares><shares>1000</shares><level>normal</level></shares></storageIOAllocation>
</device>
</deviceChange>
</obj>
我想使用MSSQL查询将XML1提取到表中。
DECLARE @xml XML
SET @xml = (SELECT ARG_DATA FROM VPX_EVENT_ARG WHERE ARG_ID = 1 AND EVENT_ID = 7121001); --EVENT_ID is fixed.
WITH XMLNAMESPACES('urn:vim25' AS NS)
SELECT
'diskUnit' = ref.value('./NS:device[1]/NS:unitNumber[1]', 'INT'),
'operation' = ref.value('./NS:operation[1]', 'NVARCHAR(100)'),
'newSizeKB' = ref.value('./NS:device[1]/NS:capacityInKB[1]', 'BIGINT')
FROM @xml.nodes('/NS:obj/NS:deviceChange') data(ref)
嗯,我得到了这个结果。
Table 2
+------+-----------+-----------+
| unit | operation | newSizeKB |
+------+-----------+-----------+
| 1 | edit | 24117248 |
| 2 | edit | 108003328 |
| 3 | add | 20971520 |
+------+-----------+-----------+
可以看到,表2是在表1中甚至没有第一行第一行的只是结果,它只是ARG_DATA它修正EVENT_ID以及第一行。我希望任何人都可以帮忙。
问题1:我想将多行XML值合并到一个表中,而不修复EVENT_ID并将EVENT_ID也放入列中。请假定VPX_EVENT_ARG表包含数百行。
Table3
+----------+------+-----------+-----------+
| EVENT_ID | unit | operation | newSizeKB |
+----------+------+-----------+-----------+
| 7121001 | 1 | edit | 24117248 |
| 7121001 | 2 | edit | 108003328 |
| 7121001 | 3 | add | 20971520 |
| 7121002 | 1 | edit | 1048576 |
| 7121002 | 3 | edit | 52428800 |
| 7121003 | 3 | edit | 125829120 |
| 7121003 | 5 | remove | 83886080 |
+----------+------+-----------+-----------+
问题2:有没有办法在不设置XML的情况下使用查询?我需要为查询设置XML,然后我可以使用nodes()。
SET @xml = (SELECT ARG_DATA FROM VPX_EVENT_ARG WHERE ARG_ID = 1 AND EVENT_ID = 7121001);
.
.
FROM @xml.nodes('/NS:obj/NS:deviceChange') data(ref)
我想知道是否可以这样做。
FROM (SELECT ARG_DATA FROM VPX_EVENT_ARG WHERE ARG_ID = 1 AND EVENT_ID = 7121001).nodes('/NS:obj/NS:deviceChange') data(ref)
Error: Incorrect syntax near '.'. Expecting AS, ID, or QUOTED_ID.
我真的想用这样的结果作为表3加入我以前查询过的表。我很难处理XML,但一直坚持这么久。顺便说一下,我无法更新表格;它受到限制。
答
你可以。使用交叉应用的节点上:
;WITH XMLNAMESPACES('urn:vim25' AS NS)
SELECT
v.EVENT_ID,
'diskUnit' = ref.value('./NS:device[1]/NS:unitNumber[1]', 'INT'),
'operation' = ref.value('./NS:operation[1]', 'NVARCHAR(100)'),
'newSizeKB' = ref.value('./NS:device[1]/NS:capacityInKB[1]', 'BIGINT')
FROM VPX_EVENT_ARG AS v
CROSS APPLY v.ARG_DATA.nodes('/NS:obj/NS:deviceChange') data(ref)
如果列不是XML,我们需要一个子查询其转换:
;WITH XMLNAMESPACES('urn:vim25' AS NS)
SELECT
v.EVENT_ID,
'diskUnit' = ref.value('./NS:device[1]/NS:unitNumber[1]', 'INT'),
'operation' = ref.value('./NS:operation[1]', 'NVARCHAR(100)'),
'newSizeKB' = ref.value('./NS:device[1]/NS:capacityInKB[1]', 'BIGINT')
FROM
(
SELECT
c.EVENT_ID,
c.ARG_TYPE,
CONVERT(xml,c.ARG_DATA) AS ARG_DATA,
c.ARG_ID
FROM VPX_EVENT_ARG AS c
) AS v
CROSS APPLY v.ARG_DATA.nodes('/NS:obj/NS:deviceChange') data(ref)
它有错误出现,“1号线 的XMLDT方法“节点'只能在xml类型的列上调用。“我发现ARG_DATA是ntext。我必须先将它转换成XML吗? – PokePalm
@PokePalm是的,在答案中增加了对话 – Backs
这项工作优雅而有效地帮助了很多。非常感谢您的帮助,Backs! – PokePalm