Problem reading all Attributes into SQL table

Question

Post reply

Problem reading all Attributes into SQL table

md44

SSC Veteran

Points: 260
More actions
December 15, 2018 at 6:33 pm

#104444

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.

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

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

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

md44 SSC Veteran Points: 260 More actions · Answer 2

<?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: 18354 More actions · Answer 3

Jonathan AC Roberts

SSCoach

Points: 18354

December 16, 2018 at 8:55 am

#2016397

Have you tried using 'nodes'?

md44 SSC Veteran Points: 260 More actions · Answer 4

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: 260 More actions · Answer 5

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: 182911 More actions · Answer 6

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: 260 More actions · Answer 7

md44

SSC Veteran

Points: 260

December 17, 2018 at 3:29 am

#2016465

This works! Thank you very much!

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 8

md44 - Monday, December 17, 2018 3:29 AM
This works! Thank you very much!

You are very welcome.
😎

md44 SSC Veteran Points: 260 More actions · Answer 9

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: 182911 More actions · Answer 10

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: 260 More actions · Answer 11

md44

SSC Veteran

Points: 260

December 17, 2018 at 4:51 pm

#2016607

Thank you so much. This works perfectly!

md44 SSC Veteran Points: 260 More actions · Answer 12

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: 76985 More actions · Answer 13

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: 260 More actions · Answer 14

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 & 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 & Rotor</text>
</Qual>
<Qty>1</Qty>
<PartType id="7108" />
<MfrLabel>New Distributor (Electronic)</MfrLabel>
<Part>2507-317771</Part>
</App>
</ACES>