0

I have XML data in below and trying to extract into Sql server into respective columns (id,CityName,Zip,County,state)

<table name="city_county_mapping">
  <column name="id">3469</column>
  <column name="city_name">100 PALMS</column>
  <column name="zip_code">92274</column>
  <column name="county_name">IMPERIAL</column>
  <column name="state_code">CA</column>
  <column name="status">1</column>
</table>
<table name="city_county_mapping">
  <column name="id">3470</column>
  <column name="city_name">1000 PALMS</column>
  <column name="zip_code">92276</column>
  <column name="county_name">RIVERSIDE</column>
  <column name="state_code">CA</column>
  <column name="status">1</column>
</table>

So far I have tried below queries, but didn't success

"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 'E:\Projects\DMV\newXML.xml', SINGLE_BLOB) AS x;


SELECT * FROM XMLwithOpenXML

Till above it's working fine, now after this I want all data in respective formate

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

SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT id, city_name, zip_code,county_name,state_code
FROM OPENXML(@hDoc, 'table/column')
WITH 
(
id [varchar](max) '@id',
city_name [varchar](max) '@city_name',
zip_code [varchar](max) 'zip_code',
county_name [varchar](max) '@city_name',
state_code [varchar](max) 'zip_code'
) "
Vikrant
  • 147
  • 1
  • 10
  • Possible duplicate of [Parsing nested XML into SQL table](http://stackoverflow.com/questions/8045831/parsing-nested-xml-into-sql-table) – fez Dec 21 '15 at 11:33
  • What have you tried so far? Where does your XML live (file, webpage, xml column in db, etc)? – David Rushton Dec 21 '15 at 11:45

0 Answers0