Xml to Database

Question

I have an xml file as below:

     <?xml version="1.0" encoding="UTF-8" ?> 
- <DeliveryPackage xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" VersionNo="Q214" CreationTime="2014-04-16T02:29:04.000000000" MapVersion="IND 14 2 00" Language_Code_Desc="../DEFINITIONS/language.xml" Country_Code_Desc="../DEFINITIONS/country.xml" Supplier_Code_Desc="../DEFINITIONS/supplier.xml" XY_Type="WGS84" Category_Code_Desc="../DEFINITIONS/category.xml" Char_Set="UTF-8" UpdateType="BulkUpdate" Coverage="IND" Category="7997">
- <POI>
  <Action>Add</Action> 
  <SupplierID>3</SupplierID> 
- <Identity>
  <POI_Entity_ID>BEL_Q112FD_308076</POI_Entity_ID> 
- <Names>
- <POI_Name Language_Code="ENG">
  <Text>100 Raw Gym</Text> 
  </POI_Name>
  </Names>
  <Category_ID Type="NT">7997</Category_ID> 
  <Product_Type>23</Product_Type> 
  </Identity>
- <Locations>
- <Location>
- <Address>
- <ParsedAddress>
- <ParsedStreetAddress>
- <ParsedStreetName>
  <StreetName Language_Code="ENG">NH-1</StreetName> 
  </ParsedStreetName>
  </ParsedStreetAddress>
- <ParsedPlace>
  <PlaceLevel2 Language_Code="ENG">Punjab</PlaceLevel2> 
  <PlaceLevel3 Language_Code="ENG">Amritsar</PlaceLevel3> 
  <PlaceLevel4 Language_Code="ENG">Amritsar</PlaceLevel4> 
  </ParsedPlace>
  <CountryCode>IND</CountryCode> 
  </ParsedAddress>
  </Address>
- <GeoPosition>
  <Latitude>31.6294</Latitude> 
  <Longitude>74.81178</Longitude> 
  </GeoPosition>
- <MapLinkID>
  <LinkID>1027669120</LinkID> 
  <Side_of_Street>R</Side_of_Street> 
  <Percent_from_RefNode>70</Percent_from_RefNode> 
  </MapLinkID>
- <Confidence>
  <Match_Level>GeoPoint</Match_Level> 
  </Confidence>
  </Location>
  </Locations>
- <Contacts>
- <Contact Language_Code="ENG" Type="Main">
  <Number Preferred="YES" Type="Phone Number">+(91)-9988692233</Number> 
  </Contact>
  </Contacts>
  </POI>

I want to insert all attributes in sql server table, starting from Action to Number. I already tried on 1st three columns,like below: At first I put complete xml file in a table 'data',then use the following code:

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

SELECT @XML = XMLData FROM data

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT [Action],SupplierID,Poi_Entity_Id
FROM OPENXML(@hDoc, 'POI')
WITH 
(
[Action] [varchar](50) '@Action',
SupplierID [int] '@SupplierID',
Poi_Entity_Id [varchar](100) 'Poi_Entity_Id'
)


EXEC sp_xml_removedocument @hDoc
GO

but getting null values in all columns... I don't want to do it manually as I have 1000 records in my XML. Please Help

Answer 1

Your data is stored in elements, not attributes, and you forgot to add DeliveryPackage node into the xpath:

select
    T.C.value('Action[1]', 'nvarchar(128)') as [Action],
    T.C.value('SupplierID[1]', 'nvarchar(128)') as SupplierID,
    T.C.value('(Identity/POI_Entity_ID)[1]', 'nvarchar(128)') as POI_Entity_ID
from @XML.nodes('DeliveryPackage/POI') as T(C)

Or, if you want to use openxml:

declare @hDoc int

exec dbo.sp_xml_preparedocument @hDoc output, @XML

select
    [Action],SupplierID,Poi_Entity_Id
from openxml(@hDoc, 'DeliveryPackage/POI')
with 
(
    [Action] [varchar](50) 'Action[1]',
    SupplierID [int] 'SupplierID[1]',
    Poi_Entity_Id [varchar](100) '(Identity/POI_Entity_ID)[1]'
)

exec dbo.sp_xml_removedocument @hDoc