asked on

Import xml into sql server 2014

I have an xml file that looks like this:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<AnswerSet title="New Answer File" version="1.1">
      <Answer name="(ANSWER FILE DESCRIPTION)" save="false">
            <TextValue></TextValue>
      </Answer>
      <Answer name="(ANSWER FILE HISTORY)" save="false">
            <TextValue></TextValue>
      </Answer>
      <Answer name="City1">
            <TextValue>Los Angeles</TextValue>
      </Answer>
      <Answer name="NameFirst">
            <TextValue>TestFirst</TextValue>
....

I've done an import of xml into a SQL Server table before but I've never seen an XML file with 'Answer name' then the name of the field. I can get so far as loading it via OPENROWSET and outputting a hyperlink, but after that, I'm lost since I don't have named elements like I'm used to seeing.

ste5an

E.g.

DECLARE @Xml XML = N'
<AnswerSet title="New Answer File" version="1.1">
      <Answer name="(ANSWER FILE DESCRIPTION)" save="false">
            <TextValue></TextValue>
      </Answer>
      <Answer name="(ANSWER FILE HISTORY)" save="false">
            <TextValue></TextValue>
      </Answer>
      <Answer name="City1">
            <TextValue>Los Angeles</TextValue>
      </Answer>
</AnswerSet>
';

SELECT	Answer.value('@name', 'NVARCHAR(255)') AS AnswerName,
		Answer.value('TextValue[1]', 'NVARCHAR(255)') AS AnswerText
FROM	@Xml.nodes('/AnswerSet/Answer') A ( Answer );

Open in new window

nolasaintsgal

ASKER

that works great with the data I presented but I first did this to get the xml into sql server:
CREATE DATABASE OPENXMLTesting
GO
USE OPENXMLTesting
GO
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'C:\Users\Owner\Downloads\A2J Author Sample XML Output.xml', SINGLE_BLOB) AS x;

SELECT * FROM XMLwithOpenXML

That gave me the hyperlink. How do I reference the data in that hyperlink?

ste5an

E.g.

DECLARE @Data TABLE
    (
        ID INT IDENTITY PRIMARY KEY,
        [Data] XML
    );

INSERT INTO @Data([Data])
    SELECT  CONVERT(XML, O.BulkColumn)
    FROM    OPENROWSET(
        BULK 'C:\Users\Owner\Downloads\A2J Author Sample XML Output.xml',
        SINGLE_BLOB
    ) O;

SELECT  D.ID,
        A.Answer.value('@name', 'NVARCHAR(255)') AS AnswerName,
        A.Answer.value('TextValue[1]', 'NVARCHAR(255)') AS AnswerText
FROM    @Data D
    CROSS APPLY D.[Data].nodes('/AnswerSet/Answer') A ( Answer );

Open in new window

nolasaintsgal

ASKER

That works beautifully for the TextValue records. I just noticed there are also values for dates, etc.

</Answer>
      <Answer name="separationDate">
            <DateValue>1/6/2016</DateValue>
      </Answer>
      <Answer name="feeWaiverTF">
            <TFValue>true</TFValue>

Those came in as NULLs. How do I get those in as well? Additionally, the xml file originally had encoding="utf-16". I had to change it to encoding="utf-8" and resave it in order to import it. Is there a way around that in the sql instead of my manually having to change the xml file each time to utf-8?

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial