Problem reading all Attributes into SQL table

  • md44

    SSC Veteran

    Points: 210

    Hello everyone.  I'm writing a proc that imports several XML files and stores them in a SQL table.  

    Here's a sample of the XML Data:

    And my query is below.  Notice that only the Elements are importing but the Attributes are NOT.  The Attributes I need imported that are not working are BaseVehicle, Aspiration, PartType and Position but they are all coming in a nulls or zeroes. The QTY, MFrLable and Part fields are all coming in OK.  I've tried numerous variants of the query and can't get it to work.  Any Help would be Greatly Appreciated!  Thank you.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    Can you please post the XML rather than a picture of it!
    😎

  • md44

    SSC Veteran

    Points: 210

    <?xml version="1.0" encoding="iso-8859-1"?>
    <ACES version="3.0">
    <App action="A" id="1">
      <BaseVehicle id="61" />
      <Qty>1</Qty>
      <PartType id="1684" />
      <MfrLabel>Ferodo</MfrLabel>
      <Position id="22" />
      <Part>FDB11</Part>
    </App>
    <App action="A" id="2">
      <BaseVehicle id="65" />
      <Aspiration id="6" />
      <Qty>1</Qty>
      <PartType id="1684" />
      <MfrLabel>Ferodo</MfrLabel>
      <Position id="22" />
      <Part>FDB451</Part>
    </App>
    <App action="A" id="3">
      <BaseVehicle id="65" />
      <Aspiration id="6" />
      <Qty>1</Qty>
      <PartType id="1684" />
      <MfrLabel>Ferodo</MfrLabel>
      <Position id="30" />
      <Part>FDB451</Part>
    </App>
    </ACES>

  • Jonathan AC Roberts

    SSCoach

    Points: 16793

    Have you tried using 'nodes'?

  • md44

    SSC Veteran

    Points: 210

    Yes I have but I get the same result where all values with an attribute show up as zero or null.

  • md44

    SSC Veteran

    Points: 210

    If someone could please post a query on how I can retrieve all the attributes and elements in the XML file, it would be much appreciated.  I took a stab at this using the nodes() method but still no luck.  I'm sure I'm doing something wrong but again, any help here would be much appreciated.

    Thanks

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    Here is an example XQuery to read both the values and the attributes.
    😎

    USE TEEST;
    GO
    SET NOCOUNT ON;

    DECLARE @TXML XML = '<?xml version="1.0" encoding="iso-8859-1"?>
    <ACES version="3.0">
    <App action="A" id="1">
    <BaseVehicle id="61" />
    <Qty>1</Qty>
    <PartType id="1684" />
    <MfrLabel>Ferodo</MfrLabel>
    <Position id="22" />
    <Part>FDB11</Part>
    </App>
    <App action="A" id="2">
    <BaseVehicle id="65" />
    <Aspiration id="6" />
    <Qty>1</Qty>
    <PartType id="1684" />
    <MfrLabel>Ferodo</MfrLabel>
    <Position id="22" />
    <Part>FDB451</Part>
    </App>
    <App action="A" id="3">
    <BaseVehicle id="65" />
    <Aspiration id="6" />
    <Qty>1</Qty>
    <PartType id="1684" />
    <MfrLabel>Ferodo</MfrLabel>
    <Position id="30" />
    <Part>FDB451</Part>
    </App>
    </ACES>';

    SELECT
      APP.DATA.value('(BaseVehicle/@id)[1]' ,'INT'         ) AS BaseVehicle
     ,APP.DATA.value('(Qty/text())[1]'      ,'INT'         ) AS Qty
     ,APP.DATA.value('(PartType/@id)[1]'    ,'INT'         ) AS PartType
     ,APP.DATA.value('(MfrLabel/text())[1]' ,'varchar(50)' ) AS MfrLabel
     ,APP.DATA.value('(Position/@id)[1]'    ,'INT'         ) AS Position
     ,APP.DATA.value('(Part/text())[1]'     ,'varchar(50)' ) AS Part
    FROM  @TXML.nodes('ACES/App') APP(DATA);

  • md44

    SSC Veteran

    Points: 210

    This works!  Thank you very much!

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    md44 - Monday, December 17, 2018 3:29 AM

    This works!  Thank you very much!

    You are very welcome.
    😎

  • md44

    SSC Veteran

    Points: 210

    Sorry, one more small question.  Some of the xml files I'll be importing are quite large and storing all the xml in a variable takes several minutes and consumes lots of resources.  As part of a nightly job, I'd like to import all the xml into a staging table then use use your query but rather than read from a variable, I would be reading from a table... How would I modify your select statement to generate the same result but reading from a table that contains an XML data type rather than a xml variable?

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    md44 - Monday, December 17, 2018 3:55 AM

    Sorry, one more small question.  Some of the xml files I'll be importing are quite large and storing all the xml in a variable takes several minutes and consumes lots of resources.  As part of a nightly job, I'd like to import all the xml into a staging table then use use your query but rather than read from a variable, I would be reading from a table... How would I modify your select statement to generate the same result but reading from a table that contains an XML data type rather than a xml variable?

    Here is an example, note that I'm just using a table variable, in your case that would be a normal table.

    USE TEEST;
    GO
    SET NOCOUNT ON;
    -- https://www.sqlservercentral.com/Forums/2013480/Problem-reading-all-Attributes-into-SQL-table
    -- TABLE VARIABLE AND SAMPLE DATA;
    DECLARE @XML_TABLE TABLE
    (
      XML_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
     ,XML_DATA XML  NOT NULL
    );

    DECLARE @TXML XML = '<?xml version="1.0" encoding="iso-8859-1"?>
    <ACES version="3.0">
    <App action="A" id="1">
    <BaseVehicle id="61" />
    <Qty>1</Qty>
    <PartType id="1684" />
    <MfrLabel>Ferodo</MfrLabel>
    <Position id="22" />
    <Part>FDB11</Part>
    </App>
    <App action="A" id="2">
    <BaseVehicle id="65" />
    <Aspiration id="6" />
    <Qty>1</Qty>
    <PartType id="1684" />
    <MfrLabel>Ferodo</MfrLabel>
    <Position id="22" />
    <Part>FDB451</Part>
    </App>
    <App action="A" id="3">
    <BaseVehicle id="65" />
    <Aspiration id="6" />
    <Qty>1</Qty>
    <PartType id="1684" />
    <MfrLabel>Ferodo</MfrLabel>
    <Position id="30" />
    <Part>FDB451</Part>
    </App>
    </ACES>';

    INSERT INTO @XML_TABLE(XML_DATA)
    SELECT @TXML UNION ALL
    SELECT @TXML UNION ALL
    SELECT @TXML UNION ALL
    SELECT @TXML
    ;
    -- TABLE SELECT
    SELECT
      TX.XML_ID
     ,APP.DATA.value('(BaseVehicle/@id)[1]' ,'INT'    ) AS BaseVehicle
     ,APP.DATA.value('(Qty/text())[1]'   ,'INT'    ) AS Qty
     ,APP.DATA.value('(PartType/@id)[1]'  ,'INT'    ) AS PartType
     ,APP.DATA.value('(MfrLabel/text())[1]' ,'varchar(50)' ) AS MfrLabel
     ,APP.DATA.value('(Position/@id)[1]'  ,'INT'    ) AS Position
     ,APP.DATA.value('(Part/text())[1]'   ,'varchar(50)' ) AS Part
    FROM  @XML_TABLE TX
    CROSS APPLY TX.XML_DATA.nodes('ACES/App') APP(DATA);

    You can also shred the XML into the table as you load it, sometimes this is better if the XML is very large.


    DECLARE @XML_TABLE_ROWS TABLE
    (
      XML_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
     ,XML_DATA XML  NOT NULL
    );

    INSERT INTO @XML_TABLE_ROWS(XML_DATA)
    SELECT
      APP.DATA.query('.')
    FROM @TXML.nodes('/ACES/App') APP(DATA);

    The adjusted query

    SELECT
      TXR.XML_ID
     ,APP.DATA.value('(BaseVehicle/@id)[1]' ,'INT'    ) AS BaseVehicle
     ,APP.DATA.value('(Qty/text())[1]'   ,'INT'    ) AS Qty
     ,APP.DATA.value('(PartType/@id)[1]'  ,'INT'    ) AS PartType
     ,APP.DATA.value('(MfrLabel/text())[1]' ,'varchar(50)' ) AS MfrLabel
     ,APP.DATA.value('(Position/@id)[1]'  ,'INT'    ) AS Position
     ,APP.DATA.value('(Part/text())[1]'   ,'varchar(50)' ) AS Part
    FROM  @XML_TABLE_ROWS TXR
    CROSS APPLY TXR.XML_DATA.nodes('/App') APP(DATA);

  • md44

    SSC Veteran

    Points: 210

    Thank you so much.  This works perfectly!

  • md44

    SSC Veteran

    Points: 210

    Hello again.  I'm having trouble linking the header info to the detail.  I need to import some of the Header attributes as well.  XML and Query are below:

    <ACES version="3.0">
    <Header>
      <Company>FERODO</Company>
      <SenderName>WHI SOLUTIONS</SenderName>
      <TransferDate>2018-11-07</TransferDate>
      <MfrCode>FSD</MfrCode>
      <BrandAAIAID>BKID</BrandAAIAID>
      <DocumentTitle>FERODO</DocumentTitle>
      <EffectiveDate>2018-11-07</EffectiveDate>
      <SubmissionType>FULL</SubmissionType>
      <MapperCompany>WHI SOLUTIONS</MapperCompany>
      <MapperEmail>aabosi@whisolutions.com</MapperEmail>
      <VcdbVersionDate>2018-09-28</VcdbVersionDate>
      <QdbVersionDate>2018-09-28</QdbVersionDate>
      <PcdbVersionDate>2018-09-28</PcdbVersionDate>
    </Header>
    <App action="A" id="1">
      <BaseVehicle id="61" />
      <Qty>1</Qty>
      <PartType id="1684" />
      <MfrLabel>Ferodo</MfrLabel>
      <Position id="22" />
      <Part>FDB11</Part>
    </App>
    </Aces>

    Query below is producing null records....  Please help.  Thank you!
       SELECT 
        Header.DATA.value('(Company/@id)[1]' ,'varchar(50)'  ) AS Company
        ,Header.DATA.value('(MfrCode/@id)[1]' ,'INT'  ) AS MfrCode
        ,Header.DATA.value('(BrandAAIAID/@id)[1]' ,'INT'  ) AS BrandAAIAID
        ,Header.DATA.value('(EffectiveDate/@id)[1]' ,'INT'  ) AS EffectiveDate
        FROM XMLStagingTABLE TX
        CROSS APPLY TX.XML_DATA.nodes('/ACES/Header') Header(DATA)

  • drew.allen

    SSC Guru

    Points: 76493

    md44 - Wednesday, December 19, 2018 9:09 AM

    Hello again.  I'm having trouble linking the header info to the detail.  I need to import some of the Header attributes as well.  XML and Query are below:

    <ACES version="3.0">
    <Header>
      <Company>FERODO</Company>
      <SenderName>WHI SOLUTIONS</SenderName>
      <TransferDate>2018-11-07</TransferDate>
      <MfrCode>FSD</MfrCode>
      <BrandAAIAID>BKID</BrandAAIAID>
      <DocumentTitle>FERODO</DocumentTitle>
      <EffectiveDate>2018-11-07</EffectiveDate>
      <SubmissionType>FULL</SubmissionType>
      <MapperCompany>WHI SOLUTIONS</MapperCompany>
      <MapperEmail>aabosi@whisolutions.com</MapperEmail>
      <VcdbVersionDate>2018-09-28</VcdbVersionDate>
      <QdbVersionDate>2018-09-28</QdbVersionDate>
      <PcdbVersionDate>2018-09-28</PcdbVersionDate>
    </Header>
    <App action="A" id="1">
      <BaseVehicle id="61" />
      <Qty>1</Qty>
      <PartType id="1684" />
      <MfrLabel>Ferodo</MfrLabel>
      <Position id="22" />
      <Part>FDB11</Part>
    </App>
    </Aces>

    Query below is producing null records....  Please help.  Thank you!
       SELECT 
        Header.DATA.value('(Company/@id)[1]' ,'varchar(50)'  ) AS Company
        ,Header.DATA.value('(MfrCode/@id)[1]' ,'INT'  ) AS MfrCode
        ,Header.DATA.value('(BrandAAIAID/@id)[1]' ,'INT'  ) AS BrandAAIAID
        ,Header.DATA.value('(EffectiveDate/@id)[1]' ,'INT'  ) AS EffectiveDate
        FROM XMLStagingTABLE TX
        CROSS APPLY TX.XML_DATA.nodes('/ACES/Header') Header(DATA)

    None of those elements have an "id" attribute, which is why you are getting NULL values.  Also, none of those contain integer data.  Try the following instead.

    SELECT
     Header.DATA.value('(Company/text())[1]' ,'varchar(50)' ) AS Company
     ,Header.DATA.value('(MfrCode/text())[1]' ,'VARCHAR(50)' ) AS MfrCode
     ,Header.DATA.value('(BrandAAIAID/text())[1]' ,'VARCHAR(50)' ) AS BrandAAIAID
     ,Header.DATA.value('(EffectiveDate/text())[1]' ,'Datetime' ) AS EffectiveDate
     FROM @TX.nodes('/ACES/Header') Header(DATA)

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • md44

    SSC Veteran

    Points: 210

    Yes, you are correct.  It works now.  My LAST issue with this file is that I need to include and repeat the Header information for each record.  Below is the XML  How can I generate a detailed resultset with all the infomation in the <Header> tag repeated on each line?  Thank you

    <?xml version="1.0" encoding="iso-8859-1"?>
    <ACES version="3.0">
    <Header>
      <Company>AI-CARDONE-CARDONE SELECT</Company>
      <SenderName>WHI SOLUTIONS</SenderName>
      <MfrCode>A2L</MfrCode>
      <DocumentTitle>AI-CARDONE-CARDONE SELECT</DocumentTitle>
      <EffectiveDate>2018-11-06</EffectiveDate>
      <SubmissionType>FULL</SubmissionType>
    </Header>
    <App action="A" id="1">
      <BaseVehicle id="1261" />
      <EngineBase id="213" />
      <Qual id="2229">
      <text>Contains Cap &amp; Rotor</text>
      </Qual>
      <Qty>1</Qty>
      <PartType id="7108" />
      <MfrLabel>New Distributor (Electronic)</MfrLabel>
      <Part>2507-317771</Part>
    </App>
    <App action="A" id="2">
      <BaseVehicle id="1262" />
      <EngineBase id="213" />
      <Qual id="2229">
      <text>Contains Cap &amp; Rotor</text>
      </Qual>
      <Qty>1</Qty>
      <PartType id="7108" />
      <MfrLabel>New Distributor (Electronic)</MfrLabel>
      <Part>2507-317771</Part>
    </App>
    </ACES>

Viewing 15 posts - 1 through 15 (of 36 total)

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