CODEWITHSUNDEEP
sqlsql-serverxml
Kevin
Kevin

Reputation: 338

Why is select query not working?

I am trying to select each PartNum, WarehouseCode, and OnhandQty from the "StkPart" attributes and select the plant from the "Plant" attribute. There is only one "Plant" in this xml file and the "Plant" would be associated with each row from the "StkPart" attribute.
For Example: 

   PartNum   WarehouseCode   OnhandQty  Plant
1. 10-12345     Corona         150      MfgSys
2. 10-12351     Cork             1      MfgSys
3. 10-51617a    Here           198      MfgSys
4. 10-97654     There           67      MfgSys

This is what I have been trying (The XML code is at the bottom): This code works and enters the data into my table:

USE  Database
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:\Test\StockStatusReport30597.XML', SINGLE_BLOB) AS x;

SELECT * FROM XMLwithOpenXML

Then I try to select the data, but this is not working:

USE Database
GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT PartNum
FROM OPENXML(@hDoc, 'ReportDataSet/PartNum')
WITH 
(
PartNum [varchar](50) '@PartNum'
)

EXEC sp_xml_removedocument @hDoc
GO

How do I get the above code to work?

Here is the XML file to download: http://wikisend.com/download/101282/StockStatusReport30597.XML

Upvotes: 1

Views: 335

Answers (1)

Laurence
Laurence

Reputation: 10976

This might help

Declare @xml xml = N'<ReportDataSet 
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    ...
    </ReportDataSet>'

Select
    N.value('PartNum[1]', 'varchar(20)') PartNum,
    N.value('WarehouseCode[1]', 'varchar(20)') WarhouseCode,
    N.value('OnhandQty[1]', 'int') OnhandQty,
    @xml.value('(/ReportDataSet/Plant/Plant)[1]', 'varchar(20)') Plant
from
    @xml.nodes('/ReportDataSet/StkPart') as T(n)

I've cut some stuff out of the example below to fit into the limitations of SQLFiddle, but it worked with the query from the question with appropriate namespaces added:

Example SQLFiddle

For your big data example, you'll need to set the default namespace for the query:

With xmlnamespaces (default 'http://www.epicor.com/Mfg/100')
Select
    N.value('PartNum[1]', 'varchar(20)') PartNum,
    N.value('WarehouseCode[1]', 'varchar(20)') WarhouseCode,
    N.value('OnhandQty[1]', 'decimal(10,2)') OnhandQty,
    @xml.value('(/ReportDataSet/Plant/Plant)[1]', 'varchar(20)') Plant
from 
    @xml.nodes('/ReportDataSet/StkPart') as T(n)

Upvotes: 1

Related Questions