June 4, 2014 at 7:29 am
Hi Guys,
I'd like to find out if its possible to check what the first node is before loading an XML file. The file name is just a combination of random numbers e.g. TMP_18028423.XML there's no way of knowing what it contains I need to check before loading into a table. Then I'll process the data from the temp table. i need to check if the first node is <Departments> then load the file that contains departments.
any help would be appreciated.
Thanks
Here's the script I'm using to load the File:
CREATE PROCEDURE JobDepartmentXMLImport
@FileName varchar(256)
AS
BEGIN
--Create temp table
CREATE TABLE #TempDepartment(
[DepartmentName] nvarchar(64) NULL,
[CostCentreCode] nvarchar(20) NOT NULL,
[bitProcessed] bit DEFAULT 0)
--Load XML data
DECLARE @sql varchar(8000)
SET @sql =
'DECLARE @xml XML;
SELECT @xml = CONVERT(XML, bulkcolumn, 2)
FROM OPENROWSET(BULK '''+ @FileName + ''', SINGLE_BLOB) AS X;
SET ARITHABORT ON;
SELECT
D.query(''./DepartmentName'').value(''.'', ''VARCHAR(255)''),
D.query(''./CostCentreCode'').value(''.'', ''VARCHAR(30)'')
FROM @xml.nodes(''Departments/Department'') as LoadDepartments(D);'
INSERT INTO #TempDepartment([DepartmentName],[CostCentreCode])
EXEC (@Sql)
END
Here's Sample file:
<Departments>
- <Department>
<DepartmentName>ASSESTS - FIREARMS</DepartmentName>
<CostCentreCode>71</CostCentreCode>
</Department>
- <Department>
<DepartmentName>ASSEST - RADIOS</DepartmentName>
<CostCentreCode>72</CostCentreCode>
</Department>
- <Department>
<DepartmentName>ENFORCE CSM'S</DepartmentName>
<CostCentreCode>80</CostCentreCode>
</Department>
</Departments>
June 4, 2014 at 12:32 pm
Use the exist() xml datatype method. You can use this in an IF block, or in a where clause... The sample code below profiles usage, and shows that it returns 1 when the node exists, and 0 when it doesn't.
-- IF method -- turn the select into an IF statement.
DECLARE @XML xml
SET @XML = '<Departments>
- <Department>
<DepartmentName>ASSESTS - FIREARMS</DepartmentName>
<CostCentreCode>71</CostCentreCode>
</Department>
- <Department>
<DepartmentName>ASSEST - RADIOS</DepartmentName>
<CostCentreCode>72</CostCentreCode>
</Department>
- <Department>
<DepartmentName>ENFORCE CSM''S</DepartmentName>
<CostCentreCode>80</CostCentreCode>
</Department>
</Departments>'
SELECT @XML.exist('/Departments/Department')
SET @XML = '<Foos>
- <Foo>
<DepartmentName>ASSESTS - FIREARMS</DepartmentName>
<CostCentreCode>71</CostCentreCode>
</Foo>
- <Foo>
<DepartmentName>ASSEST - RADIOS</DepartmentName>
<CostCentreCode>72</CostCentreCode>
</Foo>
- <Foo>
<DepartmentName>ENFORCE CSM''S</DepartmentName>
<CostCentreCode>80</CostCentreCode>
</Foo>
</Foos>'
SELECT @XML.exist('/Departments/Department')
GO
-- WHERE method
DECLARE @XML xml
SET @XML = '<Departments>
- <Department>
<DepartmentName>ASSESTS - FIREARMS</DepartmentName>
<CostCentreCode>71</CostCentreCode>
</Department>
- <Department>
<DepartmentName>ASSEST - RADIOS</DepartmentName>
<CostCentreCode>72</CostCentreCode>
</Department>
- <Department>
<DepartmentName>ENFORCE CSM''S</DepartmentName>
<CostCentreCode>80</CostCentreCode>
</Department>
</Departments>'
SELECT 'IT exists'
WHERE @XML.exist('/Departments/Department') = 1
SET @XML = '<Foos>
- <Foo>
<DepartmentName>ASSESTS - FIREARMS</DepartmentName>
<CostCentreCode>71</CostCentreCode>
</Foo>
- <Foo>
<DepartmentName>ASSEST - RADIOS</DepartmentName>
<CostCentreCode>72</CostCentreCode>
</Foo>
- <Foo>
<DepartmentName>ENFORCE CSM''S</DepartmentName>
<CostCentreCode>80</CostCentreCode>
</Foo>
</Foos>'
SELECT 'IT doesn''t exists'
WHERE @XML.exist('/Departments/Department') = 1
June 5, 2014 at 12:30 am
Thanks Lorrin, this works perfectly. I used the WHERE Clause 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply