akhrot akhrot - 4 years ago 101
SQL Question

Parsing XML from a specified Path

Respected Techhie,
may some one please help me on this.

I have a xml data in a table in a xml column something like this

DECLARE @x xml='<?xml version="1.0" encoding="utf-8"?>
<ACES version="3.0">
<Header><Company>BIPRO</Company><SenderName>Karan Johar</SenderName><SenderPhone>988XX24153 ext 247</SenderPhone><TransferDate>2014-10-17</TransferDate><BrandAAIAID>BFNG</BrandAAIAID><DocumentTitle>Jhuolant </DocumentTitle><EffectiveDate>2014-10-17</EffectiveDate><SubmissionType>FULL</SubmissionType><MapperCompany>BINRO International</MapperCompany><MapperEmail>xsdfignol@korsaadusa.com</MapperEmail><VcdbVersionDate>2014-09-26</VcdbVersionDate><QdbVersionDate>2013-01-24</QdbVersionDate><PcdbVersionDate>2014-09-26</PcdbVersionDate></Header>
<App action="A" id="1" validate="yes"><BaseVehicle id="122862">2013, Mazda, 6</BaseVehicle><EngineBase id="2547">3.7L 6Cyl V (3726)(227) 3.76 95.5(Bore)3.41 86.7(Stroke)</EngineBase><Note>Relief Pressure: 15 lb</Note><Qty>1</Qty><PartType id="2067">Engine Coolant Recovery Tank Cap</PartType><MfrLabel>Standard</MfrLabel><Part>T56</Part></App>
<App action="A" id="2" validate="yes"><BaseVehicle id="122833">2012, Ford, EcoSport</BaseVehicle><Note>Relief Pressure: 20 lb</Note><Qty>1</Qty><PartType id="2067">Engine Coolant Recovery Tank Cap</PartType><MfrLabel>Standard</MfrLabel><Part>T58</Part></App>
<App action="A" id="37645" validate="yes"><BaseVehicle id="1">2002, Suzuki, Aerio</BaseVehicle><Note>Relief Pressure: 16 lb</Note><Qty>1</Qty><PartType id="2068">Radiator Cap</PartType><MfrLabel>Safety Lever</MfrLabel><Part>ST37</Part></App>
<App action="A" id="37646" validate="yes"><BaseVehicle id="1">2002, Suzuki, Aerio</BaseVehicle><Note>Relief Pressure: 16 lb</Note><Qty>1</Qty><PartType id="2068">Radiator Cap</PartType><MfrLabel>Standard</MfrLabel><Part>T37</Part></App>
<Footer><RecordCount>37646</RecordCount></Footer>
</ACES>';

CREATE TABLE tblFile (FileID INT
,ACES_Version VARCHAR(10)
,Header_Company VARCHAR(100)
,Header_SenderName VARCHAR(100)
,Footer_RecordCount INT
,HeaderNode XML
,FooterNode XML);

INSERT INTO tblFile
SELECT 1 AS FileID
,@x.value('/ACES[1]/@version','varchar(max)') AS ACES_Version
,@x.value('(/ACES/Header/Company)[1]','varchar(max)') AS Header_Company
,@x.value('(/ACES/Header/SenderName)[1]','varchar(max)') AS Header_SenderName
,@x.value('(/ACES/Footer/RecordCount)[1]','int') AS ACES_RecordCount
,@x.query('/ACES/Header') AS HeaderNode
,@x.query('/ACES/Footer') AS FooterNode
from NETCHANGESource.dbo.tblFile


I am trying to have stored Procedure which parse the data and load it to
tblFile
.
I tried like given above but getting error like


Must declare the scalar variable "@x".


Can any one please suggest what is the error here.

i tried this code

create table HEADER
(
FileID varchar (10),
ACES_Version VARCHAR (50),
Header_Company VARCHAR (150),
Header_SenderName VARCHAR (150),
ACES_RecordCount VARCHAR (150),
HeaderNode VARCHAR (150),
FooterNode VARCHAR (150)
)

INSERT HEADER

SELECT 1 AS FileID
,t.c.value('/ACES[1]/@version','varchar(max)') AS ACES_Version
,t.c.value('(/ACES/Header/Company)[1]','varchar(max)') AS Header_Company
,t.c.value('(/ACES/Header/SenderName)[1]','varchar(max)') AS Header_SenderName
,t.c.value('(/ACES/Footer/RecordCount)[1]','int') AS ACES_RecordCount
,t.c.query('/ACES/Header') AS HeaderNode
,t.c.query('/ACES/Footer') AS FooterNode
from NETCHANGESource.dbo.tblFile s
CROSS APPLY s.xmldata.nodes('/') AS t(c); -- col is your XML column


but getting error
Msg 257, Level 16, State 3, Line 13
Implicit conversion from data type xml to varchar is not allowed. Use the CONVERT function to run this query.

Answer Source

You should use nodes() function:

INSERT INTO #tblFile
SELECT 1 AS FileID 
  ,t.c.value('/ACES[1]/@version','varchar(max)') AS ACES_Version
  ,t.c.value('(/ACES/Header/Company)[1]','varchar(max)') AS Header_Company
  ,t.c.value('(/ACES/Header/SenderName)[1]','varchar(max)') AS Header_SenderName
  ,t.c.value('(/ACES/Footer/RecordCount)[1]','int') AS ACES_RecordCount
  ,t.c.query('/ACES/Header') AS HeaderNode
  ,t.c.query('/ACES/Footer') AS FooterNode
from @x.nodes('/') AS t(c)

SELECT *
FROM #tblFile;

LiveDemo

If data is in another table you could use CROSS APPLY:

INSERT INTO tblFile   -- define column list here (FileID, ACES_Version,...)
SELECT 1 AS FileID 
      ,t.c.value('/ACES[1]/@version','varchar(max)') AS ACES_Version
      ,t.c.value('(/ACES/Header/Company)[1]','varchar(max)') AS Header_Company
      ,t.c.value('(/ACES/Header/SenderName)[1]','varchar(max)') AS Header_SenderName
      ,t.c.value('(/ACES/Footer/RecordCount)[1]','int') AS ACES_RecordCount
      ,t.c.query('/ACES/Header') AS HeaderNode
      ,t.c.query('/ACES/Footer') AS FooterNode
from NETCHANGESource.dbo.tblFile s
CROSS APPLY s.col.nodes('/') AS t(c);   -- col is your XML column

LiveDemo2

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download