CODEWITHSUNDEEP
sqlxml
Jason Vondersmith
Jason Vondersmith

Reputation: 147

read XML in SQL, no data being pulled

I am trying to retrieve data from an XML file. Below is how the XML doc looks and below that is my SQL code. It will run the code and show column headers - but will not populate with any data. What am I missing?

<profile xmlns="http://feed.elasticstats.com/schema/mma/v1/participants-profile.xsd" generated="2015-12-10T17:34:54Z">
<fighters>
<fighter id="01585452-852a-4b40-a6dc-fdd04279f02c" height="72" weight="170" reach="" stance="" first_name="Sai" nick_name="The Boss" last_name="Wang">
  <record wins="6" losses="4" draws="1" no_contests="0" />
  <born date="1988-01-16" country_code="UNK" country="Unknown" state="" city="" />
  <out_of country_code="UNK" country="Unknown" state="" city="" />
</fighter>
<fighter id="0168dd6b-b3e1-4954-8b71-877a63772dec" height="" weight="0" reach="" stance="" first_name="Enrique" nick_name="Wasabi" last_name="Marin">
  <record wins="8" losses="2" draws="0" no_contests="0" />
  <born date="" country_code="UNK" country="Unknown" state="" city="" />
  <out_of country_code="UNK" country="Unknown" state="" city="" />
</fighter>

DECLARE @x xml

SELECT @x = P
FROM OPENROWSET (BULK 'C:\Python27\outputMMA.xml', SINGLE_BLOB) AS         FIGHTERS(P)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

SELECT *
FROM OPENXML (@hdoc, '/fighters/fighter', 1) --1\ IS ATTRIBUTES AND 2 IS     ELEMENTS
WITH (
    id varchar(100),
    height varchar(10),
    last_name varchar(100)  

) --THIS IS WHERE YOU SELECT FIELDS you want returned

EXEC sp_xml_removedocument @hdoc

Upvotes: 2

Views: 88

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

FROM OPENXML is not the best approach any more. Try it like this:

Just copy this into an empty query window and execute:

DECLARE @xml XML=
'<profile xmlns="http://feed.elasticstats.com/schema/mma/v1/participants-profile.xsd" generated="2015-12-10T17:34:54Z">
  <fighters>
    <fighter id="01585452-852a-4b40-a6dc-fdd04279f02c" height="72" weight="170" reach="" stance="" first_name="Sai" nick_name="The Boss" last_name="Wang">
      <record wins="6" losses="4" draws="1" no_contests="0" />
      <born date="1988-01-16" country_code="UNK" country="Unknown" state="" city="" />
      <out_of country_code="UNK" country="Unknown" state="" city="" />
    </fighter>
    <fighter id="0168dd6b-b3e1-4954-8b71-877a63772dec" height="" weight="0" reach="" stance="" first_name="Enrique" nick_name="Wasabi" last_name="Marin">
      <record wins="8" losses="2" draws="0" no_contests="0" />
      <born date="" country_code="UNK" country="Unknown" state="" city="" />
      <out_of country_code="UNK" country="Unknown" state="" city="" />
    </fighter>
  </fighters>
</profile>';

WITH XMLNAMESPACES(DEFAULT 'http://feed.elasticstats.com/schema/mma/v1/participants-profile.xsd')
SELECT One.fighter.value('@id','uniqueidentifier') AS Fighter_ID
      ,One.fighter.value('@height','int') AS Fighter_Height      
      ,One.fighter.value('@weight','int') AS Fighter_Weigth      
      ,One.fighter.value('@reach','varchar(100)') AS Fighter_Height      
      ,One.fighter.value('@stance','varchar(100)') AS Fighter_Height      
      ,One.fighter.value('@first_name','varchar(100)') AS Fighter_FirstName
      ,One.fighter.value('@nick_name','varchar(100)') AS Fighter_NickName
      ,One.fighter.value('@last_name','varchar(100)') AS Fighter_LastName
      ,One.fighter.value('record[1]/@wins','int') AS FighterRecord_Wins
      ,One.fighter.value('record[1]/@draws','int') AS FighterRecord_Draws
      ,One.fighter.value('record[1]/@no_contests','int') AS FighterRecord_NoContest
      ,One.fighter.value('born[1]/@date','date') AS FighterBorn_Date
      ,One.fighter.value('born[1]/@country_code','varchar(10)') AS FighterBorn_CountryCode
      ,One.fighter.value('born[1]/@country','varchar(100)') AS FighterBorn_Country
      ,One.fighter.value('born[1]/@state','varchar(100)') AS FighterBorn_State
      ,One.fighter.value('born[1]/@city','varchar(100)') AS FighterBorn_City
      ,One.fighter.value('out_of[1]/@country_code','varchar(10)') AS FighterOutOf_CountryCode
      ,One.fighter.value('out_of[1]/@country','varchar(100)') AS FighterOutOf_Country
      ,One.fighter.value('out_of[1]/@state','varchar(100)') AS FighterOutOf_State
      ,One.fighter.value('out_of[1]/@city','varchar(100)') AS FighterOutOf_City
FROM @xml.nodes('/profile/fighters/fighter') AS One(fighter)

Upvotes: 1

Parfait
Parfait

Reputation: 107707

You have an undeclared namespace in your XML document. Consider the revision declaring the namespace and referencing it in xpath expression:

DECLARE @x xml;    
SELECT @x = P
FROM OPENROWSET (BULK 'C:\Python27\outputMMA.xml', SINGLE_BLOB) AS FIGHTERS(P)

DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x,
    '<root xmlns:doc="http://feed.elasticstats.com/schema/mma/v1/participants-profile.xsd"/>'

SELECT *
FROM OPENXML (@hdoc, '/doc:profile/doc:fighters/doc:fighter', 1)  
WITH (
      id varchar(100),
      height varchar(10),
      last_name varchar(100)      
     ) 

EXEC sp_xml_removedocument @hdoc

Upvotes: 0

Anton&#237;n Lejsek
Anton&#237;n Lejsek

Reputation: 6103

Firstly repair the data (:xs, </fighters>, </profile>)

<profile xmlns:xs="http://feed.elasticstats.com/schema/mma/v1/participants-profile.xsd" generated="2015-12-10T17:34:54Z">
<fighters>
<fighter id="01585452-852a-4b40-a6dc-fdd04279f02c" height="72" weight="170" reach="" stance="" first_name="Sai" nick_name="The Boss" last_name="Wang">
  <record wins="6" losses="4" draws="1" no_contests="0" />
  <born date="1988-01-16" country_code="UNK" country="Unknown" state="" city="" />
  <out_of country_code="UNK" country="Unknown" state="" city="" />
</fighter>
<fighter id="0168dd6b-b3e1-4954-8b71-877a63772dec" height="" weight="0" reach="" stance="" first_name="Enrique" nick_name="Wasabi" last_name="Marin">
  <record wins="8" losses="2" draws="0" no_contests="0" />
  <born date="" country_code="UNK" country="Unknown" state="" city="" />
  <out_of country_code="UNK" country="Unknown" state="" city="" />
</fighter>
</fighters>
</profile>

Then the code

FROM OPENXML (@docHandle, 'profile/fighters/fighter', 1)

and we are done

 01585452-852a-4b40-a6dc-fdd04279f02c   72  Wang
 0168dd6b-b3e1-4954-8b71-877a63772dec       Marin

Upvotes: 0

Related Questions