CODEWITHSUNDEEP
sqlsql-serverxml
theOGloc
theOGloc

Reputation: 241

OpenXML returning NULL

I am trying to import xml into my database with the following query using OpenXML in Microsoft SQL Server:

DECLARE @xml XML;
DECLARE @y INT;

SET @xml
    = '<ArrayOfArticle xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Article>
    <ScriptId xmlns="https://test.com/">5135399</ScriptId>
    <Title xmlns="https://test.com/">Stocks divided into two corners</Title>
    <Mediatype xmlns="https://test.com/">News papeer</Mediatype>
    <Abstract xmlns="https://test.com/">Foreign capital doubled this year.</Abstract>
    <ScriptDate xmlns="https://test.com/">2017-12-30T00:00:00</ScriptDate>
    <ScriptTypeId xmlns="https://test.com/">1</ScriptTypeId>
    <ScriptType xmlns="https://test.com/">News general</ScriptType>
    <Media xmlns="https://test.com/">Times</Media>
    <ArticleUrl xmlns="https://test.com/">http://test.com</ArticleUrl>
    <AnalysisResult xmlns="https://test.com/">
      <Analysis>
        <Regno>111</Regno>
        <Name>New York Times</Name>
        <Result>1</Result>
        <ResultName>Positive</ResultName>
      </Analysis>
      <Analysis>
        <Regno>222</Regno>
        <Name>Washington Post</Name>
        <Result>1</Result>
        <ResultName>Negative</ResultName>
      </Analysis>
    </AnalysisResult>
    <FacebookStats xmlns="https://test.com/">
      <ShareCount xsi:nil="true" />
      <LikeCount xsi:nil="true" />
      <CommentCount xsi:nil="true" />
      <TotalCount xsi:nil="true" />
    </FacebookStats>
    <MediaScore xmlns="https://test.com/">
      <MediaScore>
        <Regno>111</Regno>
        <CompanyName>New York Times</CompanyName>
        <MediaScoreID>2</MediaScoreID>
        <Name>Neither</Name>
      </MediaScore>
      <MediaScore>
        <Regno>222</Regno>
        <CompanyName>Washington Post</CompanyName>
        <MediaScoreID>2</MediaScoreID>
        <Name>Neither</Name>
      </MediaScore>
    </MediaScore>
    <Page xmlns="https://test.com/">26</Page>
    <ProgramId xmlns="https://test.com/">0</ProgramId>
    <ProgramTime xmlns="https://test.com/" xsi:nil="true" />
    <ProgramLength xmlns="https://test.com/">0</ProgramLength>
    <ProgramOrder xmlns="https://test.com/">0</ProgramOrder>
  </Article>
 </ArrayOfArticle>';

EXEC sp_xml_preparedocument @y OUTPUT, @xml;
SELECT *
FROM
    OPENXML(@y, '/ArrayOfArticle/Article', 1)
    WITH
    (
        ScriptId VARCHAR(20),
        Title VARCHAR(30),
        Mediatype VARCHAR(30)
    );

The query however only returns NULL values. What am I missing here? Would it be optimal to import the XML using SSIS instead. Not sure how much more details I can give at the given hour.

enter image description here

Upvotes: 2

Views: 1238

Answers (3)

hkravitz
hkravitz

Reputation: 1385

your XML contains namespaces, I'd use xquery in order to extract the data from your XML

UPDATE with additional elements extract

    DECLARE @xml XML;
    SET @xml
        = '<ArrayOfArticle xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Article>
        <ScriptId xmlns="https://test.com/">5135399</ScriptId>
        <Title xmlns="https://test.com/">Stocks divided into two corners</Title>
        <Mediatype xmlns="https://test.com/">News papeer</Mediatype>
        <Abstract xmlns="https://test.com/">Foreign capital doubled this year.</Abstract>
        <ScriptDate xmlns="https://test.com/">2017-12-30T00:00:00</ScriptDate>
        <ScriptTypeId xmlns="https://test.com/">1</ScriptTypeId>
        <ScriptType xmlns="https://test.com/">News general</ScriptType>
        <Media xmlns="https://test.com/">Times</Media>
        <ArticleUrl xmlns="https://test.com/">http://test.com</ArticleUrl>
        <AnalysisResult xmlns="https://test.com/">
          <Analysis>
            <Regno>111</Regno>
            <Name>New York Times</Name>
            <Result>1</Result>
            <ResultName>Positive</ResultName>
          </Analysis>
          <Analysis>
            <Regno>222</Regno>
            <Name>Washington Post</Name>
            <Result>1</Result>
            <ResultName>Negative</ResultName>
          </Analysis>
        </AnalysisResult>
        <FacebookStats xmlns="https://test.com/">
          <ShareCount xsi:nil="true" />
          <LikeCount xsi:nil="true" />
          <CommentCount xsi:nil="true" />
          <TotalCount xsi:nil="true" />
        </FacebookStats>
        <MediaScore xmlns="https://test.com/">
          <MediaScore>
            <Regno>111</Regno>
            <CompanyName>New York Times</CompanyName>
            <MediaScoreID>2</MediaScoreID>
            <Name>Neither</Name>
          </MediaScore>
          <MediaScore>
            <Regno>222</Regno>
            <CompanyName>Washington Post</CompanyName>
            <MediaScoreID>2</MediaScoreID>
            <Name>Neither</Name>
          </MediaScore>
        </MediaScore>
        <Page xmlns="https://test.com/">26</Page>
        <ProgramId xmlns="https://test.com/">0</ProgramId>
        <ProgramTime xmlns="https://test.com/" xsi:nil="true" />
        <ProgramLength xmlns="https://test.com/">0</ProgramLength>
        <ProgramOrder xmlns="https://test.com/">0</ProgramOrder>
      </Article>
     </ArrayOfArticle>'

    DECLARE @T TABLE (XmlCol XML)
    INSERT INTO @T 
    SELECT @xml


    ;WITH XMLNAMESPACES ('https://test.com/' as p1)
    SELECT z.t.value ('../../p1:ScriptId[1]',' varchar(100)') ScriptId,
           z.t.value ('../../p1:Title[1]',' varchar(100)') Title,
           z.t.value ('../../p1:Mediatype[1]',' varchar(100)') Mediatype,
           z.t.value ('p1:CompanyName[1]', 'varchar(100)') CompanyName
                FROM @T t
        CROSS APPLY XmlCol.nodes ('/ArrayOfArticle/Article/p1:MediaScore/p1:MediaScore') z(t)

Upvotes: 2

IVNSTN
IVNSTN

Reputation: 9299

DECLARE @y INT

EXEC sp_xml_preparedocument @y OUTPUT, @xml,
'<ns xmlns:x="https://test.com/"/>'

SELECT *
FROM
    OPENXML(@y, '/ArrayOfArticle/Article', 2)
    WITH
    (
        [ScriptId] VARCHAR(20) 'x:ScriptId', --<< and so on
        [Title] VARCHAR(30),
        Mediatype VARCHAR(30)
    )

EXEC sp_xml_removedocument @y  --<< lost in your code

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Do not use FROM OPENXML. This approach (together with the corresponding SPs to prepare and to remove a document) is outdated and should not be used any more.

Try the XML type's native methods, in this case .value():

Your XML is rather weird - concerning namespaces. If its creation is under your control you should try to clean this namespace mess. The unusual thing is, that your XML declares default namespaces over and over.

You can use the deep search with // together with a namespace wildcard *:

--GetItEasyCheesy (not recommended)
SELECT @xml.value(N'(//*:ScriptId)[1]',N'int') AS ScriptId
      ,@xml.value(N'(//*:Title)[1]',N'nvarchar(max)') AS Title
      ,@xml.value(N'(//*:Mediatype )[1]',N'nvarchar(max)') AS Mediatype ;

You can declare the namespace as default, but in this case you must wildcard the outer elements, as they are not part of this namespace:

--Use a default namespace
WITH XMLNAMESPACES(DEFAULT 'https://test.com/') 
SELECT @xml.value(N'(/*:ArrayOfArticle/*:Article/ScriptId/text())[1]',N'int') AS ScriptId
      ,@xml.value(N'(/*:ArrayOfArticle/*:Article/Title/text())[1]',N'nvarchar(max)') AS Title
      ,@xml.value(N'(/*:ArrayOfArticle/*:Article/Mediatype/text())[1]',N'nvarchar(max)') AS Mediatype;

The recommended approach is to bind the inner namespace to a prefix and use this

--Recommended
WITH XMLNAMESPACES('https://test.com/' AS ns) 
SELECT @xml.value(N'(/ArrayOfArticle/Article/ns:ScriptId/text())[1]',N'int') AS ScriptId
      ,@xml.value(N'(/ArrayOfArticle/Article/ns:Title/text())[1]',N'nvarchar(max)') AS Title
      ,@xml.value(N'(/ArrayOfArticle/Article/ns:Mediatype/text())[1]',N'nvarchar(max)') AS Mediatype;

If your <ArrayOfArticles> contains more than one <Article> you can use .nodes() to get alle of them as derived table. In this case the query is

WITH XMLNAMESPACES('https://test.com/' AS ns) 
SELECT art.value(N'(ns:ScriptId/text())[1]',N'int') AS Recommended
      ,art.value(N'(ns:Title/text())[1]',N'nvarchar(max)') AS Title
      ,art.value(N'(ns:Mediatype/text())[1]',N'nvarchar(max)') AS Mediatype
FROM @xml.nodes(N'/ArrayOfArticle/Article') AS A(art);

Upvotes: 5

Related Questions