Import XML - t-sql 1

[micang]

Hi All,

--Using SQL 2005--

I am trying to import an xml file into SQL, below is a sample of the xml file:

<V55 xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL] xsi:noNamespaceSchemaLocation="/blah/blah_schema/xmlschema.xsd">
  <V55Header>
    <UniqueIdentifier>1</UniqueIdentifier>
    <V55FileType>1</V55FileType>
   </V55Header>
  <V55Detail>
    <DVLAMakeCode>D6</DVLAMakeCode>
    <DVLAModelCode>415</DVLAModelCode>
    </V55Detail>
</V55>

Below is the process that I am trying to use to import directly into SQL.

DROP TABLE XmlImportTest
CREATE TABLE XmlImportTest
(
    xmlFileName VARCHAR(300),
    xml_data xml--varchar (max)
)
GO

DECLARE @xmlFileName VARCHAR(max)
SELECT  @xmlFileName = 'E:\TestXml.xml'

in OPENROWSET
EXEC('
INSERT INTO XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData 
FROM
(
    SELECT  * 
    FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')

drop table TestTable

Create Table TestTable(
[UniqueIdentifier] varchar(50) null,
BatchID varchar(100) null
)


INSERT INTO [TestTable] (
[UniqueIdentifier],
[BatchID]
)
SELECT 
tab.col.value('./UniqueIdentifier[1]','varchar(50)') AS [UniqueIdentifier],
tab.col.value('./batchid[1]','varchar(100)') AS [V55FileType]
FROM [XmlImportTest]
CROSS APPLY
xml_data.nodes('//V55Header') AS tab(col)
GO

select * from testtable

This works fine for the fields between <V55Header> and <V55Header>, but fields after this, the ones between <V55Detail> and <V55Detail> do not import. This is my first atempt at trying to import XML, I think these are possibly called nodes ?

I would appreciate any guidance leading me in the correct direction from what I have so far.

Many thanks in advance.

Michael

 

[markros]

Check this thread for some help

http://social.msdn.microsoft.com/Fo...l/thread/ace6e75f-ad81-4ddc-bd97-ca80a915fd90

 

[micang]

markros,

Thank you so much for that link, I have managed to import the XML file.

Appreciate your time.

Michael

 

[markros]

Glad it helped you.