XML to Table

• ravimir

  Grasshopper

  Points: 24

  More actions

  July 1, 2010 at 9:39 am

  #94981

  Hi,

  My requirement is to transfer XMl data for particular nodes onto a table.

  Here is my XML file and i want to extract data of '<UserName>B1i</UserName> ' and '<KeyType>SAP-ADDONS</KeyType>' nodes.

  <?xml version="1.0" encoding="UTF-16" ?>

  - <Users>

  - <User>

  <UserName>B1i</UserName>

  <IsConnected>0</IsConnected>

  - <Modules>

  - <Module>

  <KeyType>SAP-ADDONS</KeyType>

  <KeyDesc>SAP AddOns</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  </Modules>

  </User>

  - <User>

  <UserName>B1i1</UserName>

  <IsConnected>0</IsConnected>

  - <Modules>

  - <Module>

  <KeyType>SAP-ADDONS</KeyType>

  <KeyDesc>SAP AddOns</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  </Modules>

  </User>

  - <User>

  <UserName>B1i3</UserName>

  <IsConnected>0</IsConnected>

  - <Modules>

  - <Module>

  <KeyType>SAP-ADDONS</KeyType>

  <KeyDesc>SAP AddOns</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  </Modules>

  </User>

  - <User>

  <UserName>B1i5</UserName>

  <IsConnected>0</IsConnected>

  - <Modules>

  - <Module>

  <KeyType>SAP-ADDONS</KeyType>

  <KeyDesc>SAP AddOns</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  </Modules>

  </User>

  - <User>

  <UserName>manager</UserName>

  <IsConnected>1</IsConnected>

  <Modules />

  </User>

  - <User>

  <UserName>support</UserName>

  <IsConnected>0</IsConnected>

  - <Modules>

  - <Module>

  <KeyType>PROFESSIONAL</KeyType>

  <KeyDesc>Professional User</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  - <Module>

  <KeyType>SAP-ADDONS</KeyType>

  <KeyDesc>SAP AddOns</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  - <Module>

  <KeyType>SDK-TOOLS</KeyType>

  <KeyDesc>SDK Tools</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  </Modules>

  </User>

  </Users>

  Thanks in advance

  Ravi

• WayneS

  SSC Guru

  Points: 95409

  More actions

  July 1, 2010 at 10:37 am

  #1187785

  I couldn't get it to work with the "encoding" set, so I'll let you tackle that.

  The rest of this shows you how to get those results:

  declare @xml xml

  --encoding="UTF-16"

  set @xml = '<?xml version="1.0" ?>

  - <Users>

  - <User>

  <UserName>B1i</UserName>

  <IsConnected>0</IsConnected>

  - <Modules>

  - <Module>

  <KeyType>SAP-ADDONS</KeyType>

  <KeyDesc>SAP AddOns</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  </Modules>

  </User>

  - <User>

  <UserName>B1i1</UserName>

  <IsConnected>0</IsConnected>

  - <Modules>

  - <Module>

  <KeyType>SAP-ADDONS</KeyType>

  <KeyDesc>SAP AddOns</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  </Modules>

  </User>

  - <User>

  <UserName>B1i3</UserName>

  <IsConnected>0</IsConnected>

  - <Modules>

  - <Module>

  <KeyType>SAP-ADDONS</KeyType>

  <KeyDesc>SAP AddOns</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  </Modules>

  </User>

  - <User>

  <UserName>B1i5</UserName>

  <IsConnected>0</IsConnected>

  - <Modules>

  - <Module>

  <KeyType>SAP-ADDONS</KeyType>

  <KeyDesc>SAP AddOns</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  </Modules>

  </User>

  - <User>

  <UserName>manager</UserName>

  <IsConnected>1</IsConnected>

  <Modules />

  </User>

  - <User>

  <UserName>support</UserName>

  <IsConnected>0</IsConnected>

  - <Modules>

  - <Module>

  <KeyType>PROFESSIONAL</KeyType>

  <KeyDesc>Professional User</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  - <Module>

  <KeyType>SAP-ADDONS</KeyType>

  <KeyDesc>SAP AddOns</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  - <Module>

  <KeyType>SDK-TOOLS</KeyType>

  <KeyDesc>SDK Tools</KeyDesc>

  <DbType>MSS</DbType>

  <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules>

  <ReferingCount>0</ReferingCount>

  </Module>

  </Modules>

  </User>

  </Users>'

  -- to get the KeyType from the first module

  select UserName = Users.data.value('UserName[1]','varchar(100)'),

  KeyType = Users.data.value('Modules[1]/Module[1]/KeyType[1]','varchar(100)')

  FROM @xml.nodes('/Users/User') AS Users(data)

  -- to get all KeyTypes for the user:

  select UserName = Users.data.value('../../UserName[1]','varchar(100)'),

  KeyType = Users.data.value('KeyType[1]','varchar(100)')

  FROM @xml.nodes('/Users/User/Modules/Module') AS Users(data)

  Edit: added second query to get all KeyTypes for the user

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 1, 2010 at 4:37 pm

  #1187954

  Here's an alternative to Waynes second query.

  Depending on the data structure of the xml file it might perform better.

  -- to get all KeyTypes for the user, different approach:

  SELECT UserName = Users.data.value('UserName[1]','varchar(100)'),

  KeyType = Modules.data2.value('KeyType[1]','varchar(100)')

  FROM @xml.nodes('Users/User') AS Users(DATA)

  CROSS APPLY Users.data.nodes ('Modules/Module') AS Modules(data2)

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• ravimir

  Grasshopper

  Points: 24

  More actions

  July 2, 2010 at 6:40 am

  #1188147

  Thanks..Thanks a lot.Its working