Importing XML in SQL Table

Question

Importing XML in SQL Table

jennette_alambra

SSC Enthusiast

Points: 121
More actions
December 13, 2017 at 10:44 am

#330530

I am trying to import this XML in a SQL table but keeps on getting NULL. Here's my code. Did I miss anything? I have been working on this for 3hrs. Please help!
Thanks.
<appt_links>
<appt_link>
    <Field name="ResourceID">38</Field>
    <Field name="FromActivityID">4545</Field>
    <Field name="FromActivityDate">2017-12-08</Field>
    <Field name="FromActivityRouteID">663</Field>
    <Field name="ToActivityID">4546</Field>
    <Field name="ToActivityDate">2017-12-08</Field>
    <Field name="ToActivityRouteID">663</Field>
    <Field name="Link Type" />
</appt_link>
</appt_links>
DECLARE @XML AS XML, @hDoc AS INT, @sql NVARCHAR (MAX)
SELECT @XML = XMLData FROM dwstage.[dbo].[OFSXMLFiles]
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT
[ResourceID],
[FromActivityID] ,
[FromActivityDate] ,
[FromActivityRouteID] ,
[ToActivityID] ,
[ToActivityDate],
[ToActivityRouteID]
FROM OPENXML(@hDoc, 'appt_links/appt_link',1)
WITH
(
[ResourceID] [nvarchar](10) '@ResourceID',
[FromActivityID] [nvarchar](10) '@FromActivityID',
[FromActivityDate] [datetime] '@FromActivityDate',
[FromActivityRouteID] [nvarchar](10) '@FromActivityRouteID' ,
[ToActivityID] [nvarchar](10) '@ToActivityID',
[ToActivityDate] [datetime] '@ToActivityDate',
[ToActivityRouteID] [nvarchar](10) 'ToActivityRouteID'
)
EXEC sp_xml_removedocument @hDoc

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

I don't use OPENXML often but knowing XPath makes this sort of thing pretty easy. Here I'm using a variable for the XML so that you can copy/paste this to see how it works. This query:
DECLARE @XML AS XML, @hDoc AS INT, @sql NVARCHAR (MAX) --SELECT @XML = XMLData FROM dwstage.[dbo].[OFSXMLFiles]

SET @XML = ' <appt_links> <appt_link> <Field name="ResourceID">38</Field> <Field name="FromActivityID">4545</Field> <Field name="FromActivityDate">2017-12-08</Field> <Field name="FromActivityRouteID">663</Field> <Field name="ToActivityID">4546</Field> <Field name="ToActivityDate">2017-12-08</Field> <Field name="ToActivityRouteID">663</Field> <Field name="Link Type" /> </appt_link> </appt_links>';

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT xmlAttrib, xmlTxt FROM OPENXML(@hDoc, '/appt_links/appt_link/Field',1) WITH ( xmlAttrib nvarchar(30) '@name', xmlTxt nvarchar(30) '(text())[1]' );

Returns:

xmlAttrib xmlTxt ------------------------- --------------- ResourceID 38 FromActivityID 4545 FromActivityDate 2017-12-08 FromActivityRouteID 663 ToActivityID 4546 ToActivityDate 2017-12-08 ToActivityRouteID 663 Link Type NULL

All that's left to do is pivot these results. Here's how you do it, I only had time for a couple columns but this should be enough to help you across the finish line.

with x as ( SELECT xmlAttrib, xmlTxt FROM OPENXML(@hDoc, '/appt_links/appt_link/Field',1) WITH ( xmlAttrib nvarchar(30) '@name', xmlTxt nvarchar(30) '(text())[1]' ) ) select 'ResourceID' = max(case xmlAttrib when 'ResourceID' then xmlTxt end), 'FromActivityID' = max(case xmlAttrib when 'FromActivityID' then xmlTxt end) from x;

Returns
ResourceID FromActivityID --------------------- ------------------------------ 38 4545

(please forgive the sloppy formatting, the new SSC forum controls trip me up from time to time)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

drew.allen SSC Guru Points: 76987 More actions · Answer 2

Here is an alternate method that does not require a pivot.

SELECT * FROM OPENXML(@hDoc, 'appt_links/appt_link',1) WITH (

[ResourceID] [nvarchar](10) 'Field[@name="ResourceID"]', [FromActivityID] [nvarchar](10) 'Field[@name="FromActivityID"]', [FromActivityDate] [datetime] 'Field[@name="FromActivityDate"]', [FromActivityRouteID] [nvarchar](10) 'Field[@name="FromActivityRouteID"]' , [ToActivityID] [nvarchar](10) 'Field[@name="@ToActivityID"]', [ToActivityDate] [datetime] 'Field[@name="ToActivityDate"]', [ToActivityRouteID] [nvarchar](10) 'Field[@name="ToActivityRouteID"]')

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

jennette_alambra SSC Enthusiast Points: 121 More actions · Answer 3

It worked!
Thank you so much Alan Burstein and Drew.Allen for the response!!! I really appreciate you all helping me out. You saved me so much time.
Have a great day everyone! 🙂 🙂

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Quick suggestion, don't use OPENXML, you can do a straight XQuery
😎
DECLARE @TXML AS XML

SET @TXML = ' <appt_links> <appt_link> <Field name="ResourceID">38</Field> <Field name="FromActivityID">4545</Field> <Field name="FromActivityDate">2017-12-08</Field> <Field name="FromActivityRouteID">663</Field> <Field name="ToActivityID">4546</Field> <Field name="ToActivityDate">2017-12-08</Field> <Field name="ToActivityRouteID">663</Field> <Field name="Link Type" /> </appt_link> </appt_links>';

SELECT APPL.DATA.value('(Field/text())[1]','varchar(50)') AS ResourceID ,APPL.DATA.value('(Field/text())[2]','varchar(50)') AS FromActivityID ,APPL.DATA.value('(Field/text())[3]','varchar(50)') AS FromActivityDate ,APPL.DATA.value('(Field/text())[4]','varchar(50)') AS FromActivityRouteID ,APPL.DATA.value('(Field/text())[5]','varchar(50)') AS ToActivityID ,APPL.DATA.value('(Field/text())[6]','varchar(50)') AS ToActivityDate ,APPL.DATA.value('(Field/text())[7]','varchar(50)') AS ToActivityRouteID ,APPL.DATA.value('(Field/text())[8]','varchar(50)') AS [Link Type] FROM @TXML.nodes('appt_links/appt_link') APPL(DATA);

Output (with SSC Scramble)
ResourceID FromActivityID FromActivityDate FromActivityRouteID ToActivityID ToActivityDate ToActivityRouteID Link Type 38 4545 2017-12-08 663 4546 2017-12-08 663 NULL