CODEWITHSUNDEEP
sql-serverxml
PokePalm
PokePalm

Reputation: 13

Consolidating multiple rows of XML values and primary keys into one SQL-queried table

I have a table named VPX_EVENT_ARG where a column, ARG_DATA, contains XML values. 

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 and XML3 are XML values. They are too long to type in the table. Here are the real values. Actually they appear in one line.

XML1, for example,

<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>

I will separate XML1 into multiple lines so that it will be easier to read.

<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>

I would like to extract XML1 into tables using an MSSQL query.

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)

Well, I get this as a result.

Table 2
+------+-----------+-----------+
| unit | operation | newSizeKB |
+------+-----------+-----------+
|    1 | edit      |  24117248 |
|    2 | edit      | 108003328 |
|    3 | add       |  20971520 |
+------+-----------+-----------+

You can see that Table 2 is just the result of the first row in Table 1. Not even the first row, it is just ARG_DATA on the first row which fixes EVENT_ID as well. I wish anyone can help.

Question 1: I would like to consolidate multiple rows of XML values into one table without fixing EVENT_IDs and put EVENT_IDs into a column too. Please assume that VPX_EVENT_ARG table contains hundreds of rows.

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 |
+----------+------+-----------+-----------+

Question 2: Is there a way to use the query without setting XML? I need to set XML for the query, then I can use 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)

I wonder if it can be done like this.

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.

I really want to use such a result as Table3 to join the table I have queried before. I have hard time dealing with XML but stuck at this so long. By the way, I cannot update the table; it is restricted.

Upvotes: 1

Views: 71

Answers (1)

Backs
Backs

Reputation: 24923

Of couse, you can. Use cross apply on your nodes:

;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)

If your column is not XML, we need a subquery to convert it:

;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)

Upvotes: 2

Related Questions