April 23, 2018 at 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
April 24, 2018 at 12:25 am
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)');
April 24, 2018 at 12:38 am
luke 24660 - Monday, April 23, 2018 11:58 PMHi,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
April 25, 2018 at 6:55 pm
Hi Eirikur,
Thanks very much. Works as described.
ta
April 26, 2018 at 4:01 am
luke 24660 - Wednesday, April 25, 2018 6:55 PMHi 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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply