February 12, 2015 at 9:46 am
I have a sql table with the following information in one of the columns
<CallSing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><CallSign><FRSID>BG</FRSID><DeploymentGroup>SALF</DeploymentGroup><Eastings>384779000</Eastings><Northings>399005000</Northings><CurrentLocation>PRINCESS STREET MANCHESTER</CurrentLocation><HomeStation>G80</HomeStation><CurrentStationGround>G80</CurrentStationGround><LastStatusTimeStamp>20150210083237GS</LastStatusTimeStamp><CallSign>GA011</CallSign><ResourceType>AM</ResourceType><CurrentResourceStatus>AV</CurrentResourceStatus></CallSign>
<CallSign><FRSID>BG</FRSID><DeploymentGroup>SALF</DeploymentGroup><Eastings>396033000</Eastings><Northings>405503000</Northings><CurrentLocation>ROUNTREE HOUSE MANCHESTER STREET OLDHAM OL96HQ</CurrentLocation><HomeStation>G80</HomeStation><CurrentStationGround>G80</CurrentStationGround><LastStatusTimeStamp>20150203153228GS</LastStatusTimeStamp><CallSign>GA012</CallSign><ResourceType>AM</ResourceType><CurrentResourceStatus>AV</CurrentResourceStatus></CallSign>
This is one continuous string, what I need to do is to enter the data on separate rows in a new table
What I need is FRSID and the following data on a new row.
Any help would be appreciated.
February 12, 2015 at 11:08 am
clucasi (2/12/2015)
I have a sql table with the following information in one of the columns<CallSing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><CallSign><FRSID>BG</FRSID><DeploymentGroup>SALF</DeploymentGroup><Eastings>384779000</Eastings><Northings>399005000</Northings><CurrentLocation>PRINCESS STREET MANCHESTER</CurrentLocation><HomeStation>G80</HomeStation><CurrentStationGround>G80</CurrentStationGround><LastStatusTimeStamp>20150210083237GS</LastStatusTimeStamp><CallSign>GA011</CallSign><ResourceType>AM</ResourceType><CurrentResourceStatus>AV</CurrentResourceStatus></CallSign>
<CallSign><FRSID>BG</FRSID><DeploymentGroup>SALF</DeploymentGroup><Eastings>396033000</Eastings><Northings>405503000</Northings><CurrentLocation>ROUNTREE HOUSE MANCHESTER STREET OLDHAM OL96HQ</CurrentLocation><HomeStation>G80</HomeStation><CurrentStationGround>G80</CurrentStationGround><LastStatusTimeStamp>20150203153228GS</LastStatusTimeStamp><CallSign>GA012</CallSign><ResourceType>AM</ResourceType><CurrentResourceStatus>AV</CurrentResourceStatus></CallSign>
Your request is a little ambiguous. What data type is this? XML? if it is it's not well-formed (e.g. there is no closing tag for the first CallSign Element). If it's a varchar then it can be parsed as XML with a little modification.
This is one continuous string, what I need to do is to enter the data on separate rows in a new table
What I need is FRSID and the following data on a new row.
Any help would be appreciated.
Again, a little ambiguous. Perhaps you could post an example of what the desired output should look like.
In the mean time I put together an example of how to parse this data; it's my best guess at what you are looking for and should help get you in the right direction...
DECLARE @t table(tid int primary key, xmldata xml);
INSERT @t VALUES
(1,'
<!-- commenting out the first CallSing element to turn this into well-formed XML -->
<!-- <CallSing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> -->
<CallSign><FRSID>BG</FRSID><DeploymentGroup>SALF</DeploymentGroup><Eastings>384779000</Eastings><Northings>399005000</Northings><CurrentLocation>PRINCESS STREET MANCHESTER</CurrentLocation><HomeStation>G80</HomeStation><CurrentStationGround>G80</CurrentStationGround><LastStatusTimeStamp>20150210083237GS</LastStatusTimeStamp><CallSign>GA011</CallSign><ResourceType>AM</ResourceType><CurrentResourceStatus>AV</CurrentResourceStatus></CallSign>
<CallSign><FRSID>BG</FRSID><DeploymentGroup>SALF</DeploymentGroup><Eastings>396033000</Eastings><Northings>405503000</Northings><CurrentLocation>ROUNTREE HOUSE MANCHESTER STREET OLDHAM OL96HQ</CurrentLocation><HomeStation>G80</HomeStation><CurrentStationGround>G80</CurrentStationGround><LastStatusTimeStamp>20150203153228GS</LastStatusTimeStamp><CallSign>GA012</CallSign><ResourceType>AM</ResourceType><CurrentResourceStatus>AV</CurrentResourceStatus></CallSign>')
SELECT
t.tid,
FRSID = CS.value('(FRSID/text())[1]','varchar(10)'), -- getting the data from individual elements
DeploymentGroup = CS.value('(DeploymentGroup/text())[1]','varchar(10)'),
allthetext = CS.value('.','varchar(1000)')-- getting dumping all the data into one string
FROM @t t
CROSS APPLY t.xmldata.nodes('CallSign') AS tx(CS);
-- Itzik Ben-Gan 2001
February 12, 2015 at 12:40 pm
Slight side step from Alan's brilliant answer and pitching a retro "blast from the past", here is an example that checks if the root tag has a closing tag, the example is adjusted to the OP's data sample.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @NCXML VARCHAR(MAX) = '<CallSing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><CallSign><FRSID>BG</FRSID><DeploymentGroup>SALF</DeploymentGroup><Eastings>384779000</Eastings><Northings>399005000</Northings><CurrentLocation>PRINCESS STREET MANCHESTER</CurrentLocation><HomeStation>G80</HomeStation><CurrentStationGround>G80</CurrentStationGround><LastStatusTimeStamp>20150210083237GS</LastStatusTimeStamp><CallSign>GA011</CallSign><ResourceType>AM</ResourceType><CurrentResourceStatus>AV</CurrentResourceStatus></CallSign>
<CallSign><FRSID>BG</FRSID><DeploymentGroup>SALF</DeploymentGroup><Eastings>396033000</Eastings><Northings>405503000</Northings><CurrentLocation>ROUNTREE HOUSE MANCHESTER STREET OLDHAM OL96HQ</CurrentLocation><HomeStation>G80</HomeStation><CurrentStationGround>G80</CurrentStationGround><LastStatusTimeStamp>20150203153228GS</LastStatusTimeStamp><CallSign>GA012</CallSign><ResourceType>AM</ResourceType><CurrentResourceStatus>AV</CurrentResourceStatus></CallSign>'--</CallSing>'
;WITH SAMPLE_DATA(XID,TXML) AS (SELECT 1 AS XID,@NCXML AS TXML)
,XML_DATA AS
(
SELECT
SD.XID
,SUBSTRING(SD.TXML,1 + CHARINDEX(CHAR(60),SD.TXML,1),CHARINDEX(CHAR(32),SD.TXML,1) - (1 + CHARINDEX(CHAR(60),SD.TXML,1))) AS ROOT_NODE
,CONVERT(XML,CASE
WHEN CHARINDEX(SUBSTRING(SD.TXML,1 + CHARINDEX(CHAR(60),SD.TXML,1),CHARINDEX(CHAR(32),SD.TXML,1) - (1 + CHARINDEX(CHAR(60),SD.TXML,1))),SD.TXML,CHARINDEX(CHAR(32),SD.TXML,1)) = 0 THEN
SD.TXML + CHAR(60) + CHAR(47) + SUBSTRING(SD.TXML,1 + CHARINDEX(CHAR(60),SD.TXML,1),CHARINDEX(CHAR(32),SD.TXML,1) - (1 + CHARINDEX(CHAR(60),SD.TXML,1))) + CHAR(62)
ELSE SUBSTRING(SD.TXML,CHARINDEX(CHAR(62),TXML,1) + 1,LEN(TXML))
END,0) AS XXML
FROM SAMPLE_DATA SD
)
,KNOWN_STRUCTURE AS
(
SELECT
XD.XID
,TN.DATA.query('*') AS MYXML
FROM XML_DATA XD
CROSS APPLY XD.XXML.nodes('*') AS TN(DATA)
WHERE TN.DATA.exist('local-name(.) = sql:column("XD.ROOT_NODE")') = 1
)
SELECT
KS.XID
--,KS.*
,CALLSING.DATA.value('FRSID[1]' ,'VARCHAR(50)' ) AS FRSID
,CALLSING.DATA.value('DeploymentGroup[1]' ,'VARCHAR(50)' ) AS DeploymentGroup
,CALLSING.DATA.value('Eastings[1]' ,'BIGINT' ) AS Eastings
,CALLSING.DATA.value('Northings[1]' ,'BIGINT' ) AS Northings
,CALLSING.DATA.value('CurrentLocation[1]' ,'VARCHAR(150)' ) AS CurrentLocation
,CALLSING.DATA.value('HomeStation[1]' ,'VARCHAR(50)' ) AS HomeStation
,CALLSING.DATA.value('CurrentStationGround[1]' ,'VARCHAR(50)' ) AS CurrentStationGround
,CALLSING.DATA.value('LastStatusTimeStamp[1]' ,'VARCHAR(50)' ) AS LastStatusTimeStamp
,CALLSING.DATA.value('CallSign[1]' ,'VARCHAR(50)' ) AS CallSign
,CALLSING.DATA.value('ResourceType[1]' ,'VARCHAR(50)' ) AS ResourceType
,CALLSING.DATA.value('CurrentResourceStatus[1]' ,'VARCHAR(50)' ) AS CurrentResourceStatus
FROM KNOWN_STRUCTURE KS
CROSS APPLY KS.MYXML.nodes('CallSign') AS CALLSING(DATA)
;
Results
XID FRSID DeploymentGroup Eastings Northings CurrentLocation HomeStation CurrentStationGround LastStatusTimeStamp CallSign ResourceType CurrentResourceStatus
---- ------ ---------------- ---------- ---------- ----------------------------------------------- ------------ ----------------------------------------- --------- ------------- ----------------------
1 BG SALF 384779000 399005000 PRINCESS STREET MANCHESTER G80 G80 20150210083237GS GA011 AM AV
1 BG SALF 396033000 405503000 ROUNTREE HOUSE MANCHESTER STREET OLDHAM OL96HQ G80 G80 20150203153228GS GA012 AM AV
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply