May 7, 2015 at 2:20 pm
I have system id information in table system_ids and productids and systemidinsformation has lot of data but I am looking two strings in tire data and need your help to pull into two separate columns.please see details below
Thank you
Database versions :ms sql 2008/2012
tablename:system_id's
column:system id information
sample data from system_id_information column
########################################
<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="ArrayOfHostSystemIdentificationInfo"><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue> unknown</identifierValue><identifierType><label>Asset Tag</label><summary>Asset tag of the system</summary><key>AssetTag</key></identifierType></HostSystemIdentificationInfo><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue>PSF:</identifierValue><identifierType><label>OEM specific string</label><summary>OEM specific string</summary><key>OemSpecificString</key></identifierType></HostSystemIdentificationInfo><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue>Product ID: 654081-B21</identifierValue><identifierType><label>OEM specific string</label><summary>OEM specific string</summary><key>OemSpecificString</key></identifierType></HostSystemIdentificationInfo><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue>MXQ43905SW</identifierValue><identifierType><label>Service tag</label><summary>Service tag of the system</summary><key>ServiceTag</key></identifierType></HostSystemIdentificationInfo></obj>
#############################################
I am looking output of two columns, which are bolded
product_id snumber
654081-B21 MXQ43905SW
for serial number this is common
before string :HostSystemIdentificationInfo"><identifierValue>
and after string </identifierValue><identifierType><label>Service tag
and snumber is always between the before and after string and number of characters of snumber varies and entire data for a row also varies
Please advise
May 7, 2015 at 2:40 pm
Quick suggestion
😎
DECLARE @SAMPLE_XML XML = N'<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="ArrayOfHostSystemIdentificationInfo">
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue> unknown</identifierValue>
<identifierType>
<label>Asset Tag</label>
<summary>Asset tag of the system</summary>
<key>AssetTag</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>PSF:</identifierValue>
<identifierType>
<label>OEM specific string</label>
<summary>OEM specific string</summary>
<key>OemSpecificString</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>Product ID: 654081-B21</identifierValue>
<identifierType>
<label>OEM specific string</label>
<summary>OEM specific string</summary>
<key>OemSpecificString</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>MXQ43905SW</identifierValue>
<identifierType>
<label>Service tag</label>
<summary>Service tag of the system</summary>
<key>ServiceTag</key>
</identifierType>
</HostSystemIdentificationInfo>
</obj>';
;WITH XMLNAMESPACES (DEFAULT 'urn:vim25')
SELECT
HSI.DATA.value('(identifierValue/text())[1]','varchar(50)')
FROM @SAMPLE_XML.nodes('obj/HostSystemIdentificationInfo') AS HSI(DATA);
May 8, 2015 at 7:03 am
hello
Thank you for your response and have following questions
1.can we split the data into two columns and your query returns all in one
2.based on your reply how do I write select stmt on my table
select column1 as snid,column2 as prdid from system_id;
Please advise
May 8, 2015 at 8:01 am
Using Eirikur Eiriksson's setup
WITH XMLNAMESPACES (DEFAULT 'urn:vim25')
SELECT
HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[3]','varchar(50)')
,HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[4]','varchar(50)')
FROM @SAMPLE_XML.nodes('obj') AS HSI(DATA);
May 8, 2015 at 11:16 am
thank you.is this how I replace my column names?when I do I get the following errors
SELECT
sys_id('(HostSystemIdentificationInfo/identifierValue/text())[3]','varchar(50)')
,sys_id('(HostSystemIdentificationInfo/identifierValue/text())[4]','varchar(50)')
FROM host_sys);
sys_id is not a recognized built-in function name.
May 8, 2015 at 11:53 am
sqly2kdba (5/8/2015)
thank you.is this how I replace my column names?when I do I get the following errorsSELECT
sys_id('(HostSystemIdentificationInfo/identifierValue/text())[3]','varchar(50)')
,sys_id('(HostSystemIdentificationInfo/identifierValue/text())[4]','varchar(50)')
FROM host_sys);
sys_id is not a recognized built-in function name.
Using serg-52's fine code with column aliases
😎
DECLARE @SAMPLE_XML XML = N'<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="ArrayOfHostSystemIdentificationInfo">
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue> unknown</identifierValue>
<identifierType>
<label>Asset Tag</label>
<summary>Asset tag of the system</summary>
<key>AssetTag</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>PSF:</identifierValue>
<identifierType>
<label>OEM specific string</label>
<summary>OEM specific string</summary>
<key>OemSpecificString</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>Product ID: 654081-B21</identifierValue>
<identifierType>
<label>OEM specific string</label>
<summary>OEM specific string</summary>
<key>OemSpecificString</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>MXQ43905SW</identifierValue>
<identifierType>
<label>Service tag</label>
<summary>Service tag of the system</summary>
<key>ServiceTag</key>
</identifierType>
</HostSystemIdentificationInfo>
</obj>';
;WITH XMLNAMESPACES (DEFAULT 'urn:vim25')
SELECT
HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[3]','varchar(50)') AS VALIE_01
,HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[4]','varchar(50)') AS VALIE_02
FROM @SAMPLE_XML.nodes('obj') AS HSI(DATA);
Results
VALIE_01 VALIE_02
------------------------- ------------
Product ID: 654081-B21 MXQ43905SW
May 8, 2015 at 12:04 pm
I apologize for not being clear
I have to pull this data from table with 1000 rows like this
Wondering , where in the query I plug in the actual column names and table names
Please advise
May 8, 2015 at 12:44 pm
sqly2kdba (5/8/2015)
I apologize for not being clearI have to pull this data from table with 1000 rows like this
Wondering , where in the query I plug in the actual column names and table names
Please advise
No worries, here is a table oriented example
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_XML_SAMPLE') IS NOT NULL DROP TABLE dbo.TBL_XML_SAMPLE;
CREATE TABLE dbo.TBL_XML_SAMPLE
(
XS_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_SAMPLE_XS_ID PRIMARY KEY CLUSTERED
,XS_XML XML NOT NULL
);
DECLARE @SAMPLE_XML XML = N'<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="ArrayOfHostSystemIdentificationInfo">
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue> unknown</identifierValue>
<identifierType>
<label>Asset Tag</label>
<summary>Asset tag of the system</summary>
<key>AssetTag</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>PSF:</identifierValue>
<identifierType>
<label>OEM specific string</label>
<summary>OEM specific string</summary>
<key>OemSpecificString</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>Product ID: 654081-B21</identifierValue>
<identifierType>
<label>OEM specific string</label>
<summary>OEM specific string</summary>
<key>OemSpecificString</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>MXQ43905SW</identifierValue>
<identifierType>
<label>Service tag</label>
<summary>Service tag of the system</summary>
<key>ServiceTag</key>
</identifierType>
</HostSystemIdentificationInfo>
</obj>';
INSERT INTO dbo.TBL_XML_SAMPLE(XS_XML)
SELECT @SAMPLE_XML UNION ALL
SELECT @SAMPLE_XML UNION ALL
SELECT @SAMPLE_XML;
;WITH XMLNAMESPACES (DEFAULT 'urn:vim25')
SELECT
TX.XS_ID
,HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[3]','varchar(50)') AS VALIE_01
,HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[4]','varchar(50)') AS VALIE_02
FROM dbo.TBL_XML_SAMPLE TX
CROSS APPLY TX.XS_XML.nodes('obj') AS HSI(DATA);
Results
XS_ID VALIE_01 VALIE_02
------- ------------------------ ------------
1 Product ID: 654081-B21 MXQ43905SW
2 Product ID: 654081-B21 MXQ43905SW
3 Product ID: 654081-B21 MXQ43905SW
May 8, 2015 at 2:45 pm
Hi
thank you again but this is not xml datatype its ntext data type
CREATE TABLE [dbo].[SYSTEM_ID_INFO](
[ID] [int] NOT NULL,
[SYSTEM_ID] [ntext] NULL)
May 8, 2015 at 3:06 pm
sqly2kdba (5/8/2015)
Hithank you again but this is not xml datatype its ntext data type
CREATE TABLE [dbo].[SYSTEM_ID_INFO](
[ID] [int] NOT NULL,
[SYSTEM_ID] [ntext] NULL)
select
ID,
cast(SYSTEM_ID as xml)
from
dbo.SYSTEM_ID_INFO;
In the code provided, just cast SYSTEM_ID as xml.
You could do it with a view or even better would be a CTE prior to the select statement.
May 9, 2015 at 12:32 pm
hello
thank you for your time ,no luck.
how do I get data without declare stmt as i cannot declare 1000 rows or more and xml data type but return data from select stmt please advise
SELECT
tx.id
,HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[3]','varchar(50)') AS VALIE_01
,HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[4]','varchar(50)') AS VALIE_02
FROM SYSTEM_ID_INFO TX
CROSS APPLY TX.cast(SYSTEM_ID as xml) AS HSI(DATA);
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'as'.
May 10, 2015 at 1:49 am
Add a CTE for the conversion, then you can use the same code
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_XML_SAMPLE') IS NOT NULL DROP TABLE dbo.TBL_XML_SAMPLE;
CREATE TABLE dbo.TBL_XML_SAMPLE
(
XS_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_SAMPLE_XS_ID PRIMARY KEY CLUSTERED
,XS_XML NTEXT NOT NULL
);
DECLARE @SAMPLE_XML NVARCHAR(MAX) = N'<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="ArrayOfHostSystemIdentificationInfo">
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue> unknown</identifierValue>
<identifierType>
<label>Asset Tag</label>
<summary>Asset tag of the system</summary>
<key>AssetTag</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>PSF:</identifierValue>
<identifierType>
<label>OEM specific string</label>
<summary>OEM specific string</summary>
<key>OemSpecificString</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>Product ID: 654081-B21</identifierValue>
<identifierType>
<label>OEM specific string</label>
<summary>OEM specific string</summary>
<key>OemSpecificString</key>
</identifierType>
</HostSystemIdentificationInfo>
<HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo">
<identifierValue>MXQ43905SW</identifierValue>
<identifierType>
<label>Service tag</label>
<summary>Service tag of the system</summary>
<key>ServiceTag</key>
</identifierType>
</HostSystemIdentificationInfo>
</obj>';
INSERT INTO dbo.TBL_XML_SAMPLE(XS_XML)
SELECT @SAMPLE_XML UNION ALL
SELECT @SAMPLE_XML UNION ALL
SELECT @SAMPLE_XML;
;WITH XMLNAMESPACES (DEFAULT 'urn:vim25')
,BASE_DATA AS
(
SELECT
TX.XS_ID
,CONVERT(XML,TX.XS_XML,1) AS XS_XML
FROM dbo.TBL_XML_SAMPLE TX
)
SELECT
TX.XS_ID
,HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[3]','varchar(50)') AS VALIE_01
,HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[4]','varchar(50)') AS VALIE_02
FROM BASE_DATA TX
CROSS APPLY TX.XS_XML.nodes('obj') AS HSI(DATA);
Strongly suggest you change the NTEXT to NVARCHAR(MAX) or in this case XML data type if possible, the NTEXT is a depreciated data type.
From BOL:
ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
May 10, 2015 at 10:16 am
hello
the following code worked BUT how do I join this to another table (select xs_id from new_table)
WITH XMLNAMESPACES (DEFAULT 'urn:vim25')
,BASE_DATA AS
(
SELECT
TX.XS_ID
,CONVERT(XML,TX.XS_XML,1) AS XS_XML
FROM dbo.TBL_XML_SAMPLE TX
)
SELECT
TX.XS_ID
,HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[3]','varchar(50)') AS VALIE_01
,HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[4]','varchar(50)') AS VALIE_02
FROM BASE_DATA TX
CROSS APPLY TX.XS_XML.nodes('obj') AS HSI(DATA);
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply