December 12, 2012 at 6:01 pm
Hi All, I just started this job liike half an year as a Data Analyst.
I have a xml file that i want to insert into a temp table and query with it.
let say: the file name is : Test.xml
The file looks like
<!{CDATA[<NAME>Henry Lopez</Name><Address>1001 abc street</Address><City>Los Angeles</City>...]]>
they are like one long string.
I just want to ask is there a possible way to insert <Adress> and <City> into a temp and query from it?
I'm using Microsoft Server 2008 R2.
Thank you so much
December 12, 2012 at 11:40 pm
The short answer is probably yes.
But to demonstrate, you'd need to post a full, parseable XML string.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 17, 2012 at 11:18 pm
This is very long and convoluted but it works using the built-in SQL XML Schema Collection object and methods. I'm sure the experts will pick it apart--that's fine. If it can be chopped down and simplified then tear it up, build it back up again and post the results.
The code requires two functions that I have included at the bottom (one original and the other is Jeff Moden's splitter function. I've included a sample XSD schema and some raw XML code as an example to play around with. If I had more time I'd probably wrap the whole thing in a stored procedure, figure out how to get rid of so much looping, rearrange things to get rid of the mixed DDL and DML, etc, but parsing XML is just a PITA as it is and I'm tired.
The main reason I used a function and dynamic SQL was due to some silly requirement that ONLY string literals are allowed within the XML.value method...so I couldn't pass in a key using a variable. :crazy: If anyone knows how to get around this, please chime in.
IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[EmployeeInfoCollection]')
DROP XML SCHEMA COLLECTION [dbo].[EmployeeInfoCollection]
GO
CREATE XML SCHEMA COLLECTION [dbo].[EmployeeInfoCollection] AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="urn:EmployeeInfoNamespace"
targetNamespace="urn:EmployeeInfoNamespace"
elementFormDefault="qualified">
<xsd:element name="EmployeeReport">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Employee" minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="PersonalData">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FirstName" type="xsd:string" />
<xsd:element name="MiddleName" type="xsd:string" minOccurs="0" />
<xsd:element name="LastName" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="LocationData">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Address" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
<xsd:element name="State" type="xsd:string" />
<xsd:element name="Zip" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="DemographicData">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="SSN" type="xsd:string" />
<xsd:element name="DateOfBirth" type="xsd:string" />
<xsd:element name="Sex" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" type="xsd:integer" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
DECLARE @xml XML(dbo.EmployeeInfoCollection)
SET @xml =
'<?xml version="1.0" encoding="UTF-8"?>
<EmployeeReport xmlns="urn:EmployeeInfoNamespace">
<Employee id="1234">
<PersonalData>
<FirstName>Jane</FirstName>
<MiddleName />
<LastName>Doe</LastName>
</PersonalData>
<LocationData>
<Address>133 Main St</Address>
<City>Boston</City>
<State>MA</State>
<Zip>01234</Zip>
</LocationData>
<DemographicData>
<SSN>111-11-1111</SSN>
<DateOfBirth>01/01/1978</DateOfBirth>
<Sex>F</Sex>
</DemographicData>
</Employee>
<Employee id="2345">
<PersonalData>
<FirstName>John</FirstName>
<MiddleName>A</MiddleName>
<LastName>Smith</LastName>
</PersonalData>
<LocationData>
<Address>456 Elm Ave</Address>
<City>New York</City>
<State>NY</State>
<Zip>02345</Zip>
</LocationData>
<DemographicData>
<SSN>222-22-2222</SSN>
<DateOfBirth>12/31/1982</DateOfBirth>
<Sex>M</Sex>
</DemographicData>
</Employee>
<Employee id="4565">
<PersonalData>
<FirstName>George</FirstName>
<MiddleName>W</MiddleName>
<LastName>Bush</LastName>
</PersonalData>
<LocationData>
<Address>789 First St</Address>
<City>Dallas</City>
<State>TX</State>
<Zip>56789</Zip>
</LocationData>
<DemographicData>
<SSN>333-33-3333</SSN>
<DateOfBirth>07/04/1935</DateOfBirth>
<Sex>M</Sex>
</DemographicData>
</Employee></EmployeeReport>'
IF OBJECT_ID('tempdb..#XMLTable') IS NOT NULL
DROP TABLE #XMLTable
CREATE TABLE #XMLTable (
[ID] INT NOT NULL,
[EmployeeXML] XML NULL,
PRIMARY KEY (ID),
UNIQUE (ID))
INSERT INTO #XMLTable
(ID
,EmployeeXML)
VALUES
(1
,CONVERT(XML,@xml))
DECLARE @strParsedXML AS VARCHAR(MAX)
SET @strParsedXML = CONVERT(VARCHAR(MAX),@xml)
SET @strParsedXML = REPLACE(@strParsedXML,'"urn:EmployeeInfoNamespace">','"urn:EmployeeInfoNamespace">@')
SET @strParsedXML = REPLACE(@strParsedXML,'</Employee><Employee ','</Employee>@<Employee ')
SET @strParsedXML = REPLACE(@strParsedXML,'</Employee></EmployeeReport>','</Employee>@</EmployeeReport>')
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[EmpID] VARCHAR(50) NULL,
[strParsedXML] VARCHAR(MAX) NULL,
[FirstName] VARCHAR(50) NULL,
[MiddleName] VARCHAR(50) NULL,
[LastName] VARCHAR(50) NULL,
[Address] VARCHAR(50) NULL,
[City] VARCHAR(50) NULL,
[State] VARCHAR(50) NULL,
[Zip] VARCHAR(50) NULL,
[SSN] VARCHAR(50) NULL,
[DateOfBirth] VARCHAR(50) NULL,
[Sex] VARCHAR(50) NULL,
PRIMARY KEY (ID),
UNIQUE (ID))
INSERT INTO #TempTable
SELECT
NULLIF(REPLACE(REPLACE(LEFT(item,CHARINDEX('><',item)),'<Employee id="',''),'">',''),'')
,Item
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM
dbo.DelimitedSplit8K(@strParsedXML,'@')
DECLARE
@NumRows INT
,@FirstRow INT
,@LastRow INT
,@EmployeeCount INT
,@ID INT
,@EmployeeID VARCHAR(50)
,@strSQL NVARCHAR(MAX)
SELECT @EmployeeCount =
EmployeeXML.value(
'declare namespace ns="urn:EmployeeInfoNamespace";
count(/ns:EmployeeReport/ns:Employee)'
,'int')
FROM #XMLTable
SELECT
@NumRows = COUNT(ID)
FROM
#TempTable
SET @FirstRow = 2
SET @LastRow = @NumRows - 1
DECLARE UpdateList CURSOR
FOR
SELECT
ID
,EmpID
FROM
#TempTable
WHERE
ID BETWEEN @FirstRow AND @LastRow
ORDER BY
ID
OPEN UpdateList
FETCH NEXT FROM UpdateList INTO
@ID
,@EmployeeID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @strSQL =
(SELECT dbo.svfXMLQueryValue
('EmployeeInfoNamespace'
,'EmployeeReport'
,'Employee'
,@EmployeeID
,'PersonalData'
,'FirstName'
))
EXEC sp_executesql @strSQL
SELECT @strSQL =
(SELECT dbo.svfXMLQueryValue
('EmployeeInfoNamespace'
,'EmployeeReport'
,'Employee'
,@EmployeeID
,'PersonalData'
,'MiddleName'
))
EXEC sp_executesql @strSQL
SELECT @strSQL =
(SELECT dbo.svfXMLQueryValue
('EmployeeInfoNamespace'
,'EmployeeReport'
,'Employee'
,@EmployeeID
,'PersonalData'
,'LastName'
))
EXEC sp_executesql @strSQL
SELECT @strSQL =
(SELECT dbo.svfXMLQueryValue
('EmployeeInfoNamespace'
,'EmployeeReport'
,'Employee'
,@EmployeeID
,'LocationData'
,'Address'
))
EXEC sp_executesql @strSQL
SELECT @strSQL =
(SELECT dbo.svfXMLQueryValue
('EmployeeInfoNamespace'
,'EmployeeReport'
,'Employee'
,@EmployeeID
,'LocationData'
,'City'
))
EXEC sp_executesql @strSQL
SELECT @strSQL =
(SELECT dbo.svfXMLQueryValue
('EmployeeInfoNamespace'
,'EmployeeReport'
,'Employee'
,@EmployeeID
,'LocationData'
,'State'
))
EXEC sp_executesql @strSQL
SELECT @strSQL =
(SELECT dbo.svfXMLQueryValue
('EmployeeInfoNamespace'
,'EmployeeReport'
,'Employee'
,@EmployeeID
,'LocationData'
,'Zip'
))
EXEC sp_executesql @strSQL
SELECT @strSQL =
(SELECT dbo.svfXMLQueryValue
('EmployeeInfoNamespace'
,'EmployeeReport'
,'Employee'
,@EmployeeID
,'DemographicData'
,'SSN'
))
EXEC sp_executesql @strSQL
SELECT @strSQL =
(SELECT dbo.svfXMLQueryValue
('EmployeeInfoNamespace'
,'EmployeeReport'
,'Employee'
,@EmployeeID
,'DemographicData'
,'DateOfBirth'
))
EXEC sp_executesql @strSQL
SELECT @strSQL =
(SELECT dbo.svfXMLQueryValue
('EmployeeInfoNamespace'
,'EmployeeReport'
,'Employee'
,@EmployeeID
,'DemographicData'
,'Sex'
))
EXEC sp_executesql @strSQL
FETCH NEXT FROM UpdateList INTO
@ID
,@EmployeeID
END
CLOSE UpdateList
DEALLOCATE UpdateList
SELECT
EmpID
,FirstName
,MiddleName
,LastName
,Address
,City
,State
,Zip
,SSN
,DateOfBirth
,Sex
FROM
#TempTable
WHERE
ID BETWEEN @FirstRow AND @LastRow
CREATE FUNCTION [dbo].[svfXMLQueryValue]
(
@strNameSpace VARCHAR(50)
,@strRoot VARCHAR(50)
,@strKeyNode VARCHAR(50)
,@strKeyValue VARCHAR(50)
,@strParent VARCHAR(50)
,@strElement VARCHAR(50)
)
RETURNS NVARCHAR(MAX)
BEGIN
DECLARE @strQueryValue VARCHAR(8000)
SET @strQueryValue =
'UPDATE #TempTable
SET
'+@strElement+' = (
SELECT
EmployeeXML.value(''declare namespace ns="urn:'+@strNameSpace+'";
(/ns:'+@strRoot+'/ns:'+@strKeyNode+'[@id='+@strKeyValue+']/ns:'+@strParent+'/ns:'+@strElement+')[1]''
,''varchar(50)'') AS '+@strElement+'
FROM
#XMLTable AS xt)
WHERE
EmpID = '+@strKeyValue
RETURN @strQueryValue
END
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K]
(
@pString VARCHAR(8000)
,@pDelimiter CHAR(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH E1(N)
AS (
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
),--10E+1 or 10 rows
E2(N)
AS (
SELECT
1
FROM
E1 a
,E1 b
),--10E+2 or 100 rows
E4(N)
AS (
SELECT
1
FROM
E2 a
,E2 b
),--10E+4 or 10,000 rows max
cteTally(N)
AS (
SELECT TOP (ISNULL(DATALENGTH(@pString),0))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E4
),
cteStart(N1)
AS (
SELECT
1
UNION ALL
SELECT
t.N + 1
FROM
cteTally t
WHERE
SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1)
AS (
SELECT
s.N1
,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0) - s.N1,8000)
FROM
cteStart s
)
SELECT
ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1)
,Item = SUBSTRING(@pString,l.N1,l.L1)
FROM
cteLen l ;
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply