1gb XML into SQL using xPath or XQuery

  • luke 24660

    SSC Journeyman

    Points: 77

    Hi,

    I'm new to SQL and I have been trying to learn how to read my 1gb XML into SQL using xPath.

    I have added an example of the file.

    Essentially what I want to do is grab the ResourceID in the v_R_System element and that will be the first col in the DB.

    However there are other XML elements that all named the same however, they contain different data based on this ResourceID

    Here is my code.

    Declare @fileDataX XML
    Declare @fileDataY XML
    -- import the file contents into the variable
    Select @fileDataX=BulkColumn from OpenRowSet(Bulk'C:\MAP\TEST\sccm_4-9-2018 15.55.56.xml',Single_blob) x;

    -- insert the xml data into our test table (ID, PersonsName, Country)
    insert into [dbo].[TEST]
      (ResourceID, ComputerName, OperatingSystem)
    select
     v_R_System.value('ResourceID[1]','int') ResourceID,   -- 'xData' is our xml content alias
     v_R_System.value('NetBios_Name0[1]','varChar(50)') ComputerName,
     v_GS_Operating_System.value('/Root/v_GS_Operating_System[ResourceID="WHAT DO I PUT HERE]"]/text()','varChar(100)') OperatingSystem
    from @fileDataX.nodes('/Root/v_R_System') As x(v_R_System) -- this is the xpath to the individual records we want to extract
    CROSS APPLY @fileDataY.nodes('/Root/v_GS_Operating_System') As y(v_GS_Operating_System)

    The problem is I cant seem to pull back data using this ResourceID in a xPath expression to match the v_GS_Operating_System/ResourceID node.

    Hope that makes sense. Appreciate any help. Been battering my head against the wall all day.

    ta

  • Eirikur Eiriksson

    SSC Guru

    Points: 182343

    This should get you passed this hurdle
    😎

    USE TEEST;
    GO
    SET NOCOUNT ON;

    DECLARE @TXML XML = '<root>
    <v_R_System>
    <ResourceID>33558284</ResourceID>
    <Client_Version0>5.00.8577.1005</Client_Version0>
    <Last_Logon_Timestamp0>2018-04-08T22:17:44</Last_Logon_Timestamp0>
    <User_Domain0>POOKEY</User_Domain0>
    <User_Name0>ABC123</User_Name0>
    <Name0>PC1234</Name0>
    <NetBios_Name0>PC1234</NetBios_Name0>
    <Resource_Domain_OR_Workgr0>POOKEYDOMAIN</Resource_Domain_OR_Workgr0>
    <Virtual_Machine_Host_Name0 />
    </v_R_System>
    <v_R_System>
    <ResourceID>33558285</ResourceID>
    <Client_Version0>5.00.8577.1005</Client_Version0>
    <Last_Logon_Timestamp0>2018-04-08T22:17:44</Last_Logon_Timestamp0>
    <User_Domain0>POOKEYDOMAIN</User_Domain0>
    <User_Name0>ABC234</User_Name0>
    <Name0>PC4567</Name0>
    <NetBios_Name0>PC4567</NetBios_Name0>
    <Resource_Domain_OR_Workgr0>POOKEYDOMAIN</Resource_Domain_OR_Workgr0>
    <Virtual_Machine_Host_Name0 />
    </v_R_System>
    <v_R_System>
    <ResourceID>33558286</ResourceID>
    <Client_Version0>5.00.8577.1005</Client_Version0>
    <Last_Logon_Timestamp0>2018-04-08T22:17:44</Last_Logon_Timestamp0>
    <User_Domain0>POOKEYDOMAIN</User_Domain0>
    <User_Name0>XYZ123</User_Name0>
    <Name0>PC123</Name0>
    <NetBios_Name0>PC123</NetBios_Name0>
    <Resource_Domain_OR_Workgr0>POOKEYDOMAIN</Resource_Domain_OR_Workgr0>
    <Virtual_Machine_Host_Name0 />
    </v_R_System>
    <v_GS_Operating_System>
    <ResourceID>33558284</ResourceID>
    <GroupID>1</GroupID>
    <Caption0>Microsoft Windows Server 2012 R2 Standard</Caption0>
    <InstallDate0>2016-04-27T13:06:28</InstallDate0>
    <Version0>6.3.9600</Version0>
    </v_GS_Operating_System>
    <v_GS_Operating_System>
    <ResourceID>33558285</ResourceID>
    <GroupID>1</GroupID>
    <Caption0>Microsoft Windows Server 2012 R2 Standard</Caption0>
    <InstallDate0>2017-01-05T16:03:49</InstallDate0>
    <Version0>6.3.9600</Version0>
    </v_GS_Operating_System>
    <v_GS_Operating_System>
    <ResourceID>33558286</ResourceID>
    <GroupID>1</GroupID>
    <Caption0>Microsoft Windows Server 2012 R2 Standard</Caption0>
    <InstallDate0>2017-01-05T16:03:49</InstallDate0>
    <Version0>6.3.9600</Version0>
    </v_GS_Operating_System>
    </root>';
    -- The v_R_System set
    SELECT
      VSYS.DATA.value('(ResourceID/text())[1]'       ,'VARCHAR(20)') AS ResourceID
     ,VSYS.DATA.value('(Client_Version0/text())[1]'     ,'VARCHAR(50)') AS Client_Version0
     ,VSYS.DATA.value('(Last_Logon_Timestamp0/text())[1]'   ,'DATETIME' ) AS Last_Logon_Timestamp0
     ,VSYS.DATA.value('(User_Domain0/text())[1]'      ,'VARCHAR(50)') AS User_Domain0
     ,VSYS.DATA.value('(User_Name0/text())[1]'       ,'VARCHAR(50)') AS User_Name0
     ,VSYS.DATA.value('(Name0/text())[1]'         ,'VARCHAR(50)') AS Name0
     ,VSYS.DATA.value('(NetBios_Name0/text())[1]'      ,'VARCHAR(50)') AS NetBios_Name0
     ,VSYS.DATA.value('(Resource_Domain_OR_Workgr0/text())[1]'  ,'VARCHAR(50)') AS Resource_Domain_OR_Workgr0
     ,VSYS.DATA.value('(Virtual_Machine_Host_Name0/text())[1]'  ,'VARCHAR(20)') AS Virtual_Machine_Host_Name0
    FROM @TXML.nodes('/root/v_R_System') VSYS(DATA);
    -- The v_GS_Operating_System set
    SELECT
      OS.DATA.value('(ResourceID/text())[1]' ,'VARCHAR(20)') AS ResourceID
     ,OS.DATA.value('(GroupID/text())[1]'  ,'INT'   ) AS GroupID
     ,OS.DATA.value('(Caption0/text())[1]'  ,'VARCHAR(50)') AS Caption0
     ,OS.DATA.value('(InstallDate0/text())[1]','DATETIME' ) AS InstallDate0
     ,OS.DATA.value('(Version0/text())[1]'  ,'VARCHAR(20)') AS Version0
    FROM @TXML.nodes('/root/v_GS_Operating_System') OS(DATA);

    -- Combined sets
    SELECT
      VSYS.DATA.value('(ResourceID/text())[1]'       ,'VARCHAR(20)') AS ResourceID
     ,VSYS.DATA.value('(Client_Version0/text())[1]'     ,'VARCHAR(50)') AS Client_Version0
     ,VSYS.DATA.value('(Last_Logon_Timestamp0/text())[1]'   ,'DATETIME' ) AS Last_Logon_Timestamp0
     ,VSYS.DATA.value('(User_Domain0/text())[1]'      ,'VARCHAR(50)') AS User_Domain0
     ,VSYS.DATA.value('(User_Name0/text())[1]'       ,'VARCHAR(50)') AS User_Name0
     ,VSYS.DATA.value('(Name0/text())[1]'         ,'VARCHAR(50)') AS Name0
     ,VSYS.DATA.value('(NetBios_Name0/text())[1]'      ,'VARCHAR(50)') AS NetBios_Name0
     ,VSYS.DATA.value('(Resource_Domain_OR_Workgr0/text())[1]'  ,'VARCHAR(50)') AS Resource_Domain_OR_Workgr0
     ,VSYS.DATA.value('(Virtual_Machine_Host_Name0/text())[1]'  ,'VARCHAR(20)') AS Virtual_Machine_Host_Name0
     ,OS.DATA.value('(ResourceID/text())[1]' ,'VARCHAR(20)') AS ResourceID
     ,OS.DATA.value('(GroupID/text())[1]'  ,'INT'   ) AS GroupID
     ,OS.DATA.value('(Caption0/text())[1]'  ,'VARCHAR(50)') AS Caption0
     ,OS.DATA.value('(InstallDate0/text())[1]','DATETIME' ) AS InstallDate0
     ,OS.DATA.value('(Version0/text())[1]'  ,'VARCHAR(20)') AS Version0
    FROM   @TXML.nodes('/root/v_R_System')     VSYS(DATA)
    CROSS APPLY @TXML.nodes('/root/v_GS_Operating_System') OS(DATA)
    WHERE   OS.DATA.value('(ResourceID/text())[1]','VARCHAR(20)') = VSYS.DATA.value('(ResourceID/text())[1]','VARCHAR(20)');

  • Eirikur Eiriksson

    SSC Guru

    Points: 182343

    luke 24660 - Monday, April 23, 2018 11:58 PM

    Hi,

    I'm new to SQL and I have been trying to learn how to read my 1gb XML into SQL using xPath.

    I have added an example of the file.

    Essentially what I want to do is grab the ResourceID in the v_R_System element and that will be the first col in the DB.

    However there are other XML elements that all named the same however, they contain different data based on this ResourceID

    Here is my code.

    Declare @fileDataX XML
    Declare @fileDataY XML
    -- import the file contents into the variable
    Select @fileDataX=BulkColumn from OpenRowSet(Bulk'C:\MAP\TEST\sccm_4-9-2018 15.55.56.xml',Single_blob) x;

    -- insert the xml data into our test table (ID, PersonsName, Country)
    insert into [dbo].[TEST]
      (ResourceID, ComputerName, OperatingSystem)
    select
     v_R_System.value('ResourceID[1]','int') ResourceID,   -- 'xData' is our xml content alias
     v_R_System.value('NetBios_Name0[1]','varChar(50)') ComputerName,
     v_GS_Operating_System.value('/Root/v_GS_Operating_System[ResourceID="WHAT DO I PUT HERE]"]/text()','varChar(100)') OperatingSystem
    from @fileDataX.nodes('/Root/v_R_System') As x(v_R_System) -- this is the xpath to the individual records we want to extract
    CROSS APPLY @fileDataY.nodes('/Root/v_GS_Operating_System') As y(v_GS_Operating_System)

    The problem is I cant seem to pull back data using this ResourceID in a xPath expression to match the v_GS_Operating_System/ResourceID node.

    Hope that makes sense. Appreciate any help. Been battering my head against the wall all day.

    ta

    Two things here, XML and the XQueries are case sensitive and the path in the value function is relative to the one in the nodes function.
    😎

    Here is a corrected version of your query.

    select
    select
    v_R_System.value('ResourceID[1]','int') ResourceID, -- 'xData' is our xml content alias
    v_R_System.value('NetBios_Name0[1]','varChar(50)') ComputerName,
    v_GS_Operating_System.value('(Version0/text())[1]','varChar(100)') OperatingSystem
    from   @TXML.nodes('/root/v_R_System') As x(v_R_System) -- this is the xpath to the individual records we want to extract
    CROSS APPLY @TXML.nodes('/root/v_GS_Operating_System') As y(v_GS_Operating_System)
    WHERE   v_R_System.value('ResourceID[1]','int') = v_GS_Operating_System.value('(ResourceID/text())[1]','int')

    Output

    ResourceID ComputerName OperatingSystem
    ----------- ------------- ----------------
    33558284  PC1234   6.3.9600
    33558285  PC4567   6.3.9600
    33558286  PC123   6.3.9600

  • luke 24660

    SSC Journeyman

    Points: 77

    Hi Eirikur,

    Thanks very much. Works as described.

    ta

  • Eirikur Eiriksson

    SSC Guru

    Points: 182343

    luke 24660 - Wednesday, April 25, 2018 6:55 PM

    Hi Eirikur,

    Thanks very much. Works as described.

    ta

    You are welcome.
    😎

    Note that you should always use the text() function when retrieving element text from the XML, it eliminates the reconstruction of the XML for the output, effectively cuts the work the server has to do in half.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply