import and shred xml file to sql 2008 table (openrowset)

• Michael F-386896

  SSC Enthusiast

  Points: 155

  More actions

  November 30, 2012 at 10:28 am

  #285423

  Hello,

  I'm also trying to import certain fields of an xml-file into an existing sql table (neutc_import).

  Problem is: I get '0 rows affected' when I execute the query below.

  In the end I need to have mac-address and tcname for every machine in table neutc_import.

  I'm sure the query is wrong at least regarding the "Cross Apply" statement.

  As you can see I am not very experienced with this type of stuff - so your

  help is really appreciated - maybe just a small adjustment necessary (?)

  query:

  INSERT INTO neutc_import(macaddr,tcname)

  SELECT X.a.query('MACAddress').value('.', 'varchar(50)'),

  X.a.query('ComputerName').value('.', 'VARCHAR(50)')

  FROM (

  SELECT CAST(x AS XML)

  FROM OPENROWSET(

  BULK 'E:\scout_import\DSBU010995_Imdly_2_HwInfo.xml',

  SINGLE_BLOB) AS T(x)

  ) AS T(x)

  CROSS APPLY x.nodes('records/record') AS X(a);

  referenced xml:

  <?xml version="1.0" encoding="utf-8"?>

  <Systems Version="4.00" xsi:schemaLocation="urn:schemas-ts-fujitsu-com:software-preload HWInfo4.00.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-ts-fujitsu-com:software-preload">

  <System>

  <SystemIdentification>

  <SerialNumber>DSBU010995</SerialNumber>

  <IndividualSoftwareID />

  <SystemUUID>{B2E832CC-7484-11E1-8B14-5026909B9A32}</SystemUUID>

  <CustomerSerialNumber />

  <IMEINumber></IMEINumber>

  <MACAddresses>

  <MACAddress type="OnBoard">00:30:05:68:FF:BD</MACAddress>

  </MACAddresses>

  </SystemIdentification>

  <OrderReferenceData>

  <P82OrderNumber>xxxxxxx</P82OrderNumber>

  <P82CustomerOrderNumber>xxxxxxx</P82CustomerOrderNumber>

  <P84OrderNumber>xxxxxxx</P84OrderNumber>

  <P84OrderPosition>100</P84OrderPosition>

  <P84CustomerOrderNumber>xxxxxxx</P84CustomerOrderNumber>

  <EndCustomerOrderNumber>xxxxxxx</EndCustomerOrderNumber>

  </OrderReferenceData>

  <ProductDetails>

  <OrderCode>CUZ:KxxxVXXX-EMFxx</OrderCode>

  <Product>LB P771 BLACK /CUZ:KxxxVXXX-EMFxx</Product>

  </ProductDetails>

  <SoftwareModules />

  <OrderAttachements>

  <HeaderSpecificData />

  <PositionSpecificData>

  <ClientParameters SubClient="None" Client="Caritas">

  <ComputerName>CNxxxxx</ComputerName>

  <OldComputerName>ONxxxxx</OldComputerName>

  <PrimaryContact>Max Mustermann</PrimaryContact>

  </ClientParameters>

  </PositionSpecificData>

  </OrderAttachements>

  </System>

  <System>

  <SystemIdentification>

  <SerialNumber>DSBU010995</SerialNumber>

  <IndividualSoftwareID />

  <SystemUUID>{B2E832CC-7484-11E1-8B14-5026909B9A32}</SystemUUID>

  <CustomerSerialNumber />

  <IMEINumber></IMEINumber>

  <MACAddresses>

  <MACAddress type="OnBoard">00:30:05:68:FF:BD</MACAddress>

  </MACAddresses>

  </SystemIdentification>

  <OrderReferenceData>

  <P82OrderNumber>xxxxxxx</P82OrderNumber>

  <P82CustomerOrderNumber>xxxxxxx</P82CustomerOrderNumber>

  <P84OrderNumber>xxxxxxx</P84OrderNumber>

  <P84OrderPosition>200</P84OrderPosition>

  <P84CustomerOrderNumber>xxxxxxx</P84CustomerOrderNumber>

  <EndCustomerOrderNumber>xxxxxxx</EndCustomerOrderNumber>

  </OrderReferenceData>

  <ProductDetails>

  <OrderCode>CUZ:KxxxVXXX-EMFxx</OrderCode>

  <Product>LB P771 BLACK /CUZ:KxxxVXXX-EMFxx</Product>

  </ProductDetails>

  <SoftwareModules />

  <OrderAttachements>

  <HeaderSpecificData />

  <PositionSpecificData>

  <ClientParameters SubClient="None" Client="Caritas">

  <ComputerName>CNxxxxx</ComputerName>

  <OldComputerName>ONxxxxx</OldComputerName>

  <PrimaryContact>Max Mustermann</PrimaryContact>

  </ClientParameters>

  </PositionSpecificData>

  </OrderAttachements>

  </System>

  </Systems>

  Greetings from nice munich, Michael

• Arthur Olcot

  SSCertifiable

  Points: 6759

  More actions

  December 1, 2012 at 4:47 am

  #1564633

  Hi,

  I think the main thing that you was missing was the namespace declaration. I have also made a couple of minor alterations, namely the cross apply and removing the .query() methods in the select statement. I have come up with the following that gets the MAC address and the computer name from the example xml

  WITH xmlnamespaces (DEFAULT 'urn:schemas-ts-fujitsu-com:software-preload')

  SELECT X.a.value('(SystemIdentification/MACAddresses/MACAddress/text())[1]', 'VARCHAR(30)') AS 'MAC',

  X.a.value('(OrderAttachements/PositionSpecificData/ClientParameters/ComputerName/text())[1]', 'VARCHAR(30)')

  FROM (

  SELECT CAST(x AS XML)

  FROM OPENROWSET(

  BULK 'd:\ExampleXml.xml',

  SINGLE_BLOB) AS T(x)

  ) AS T(x)

  CROSS APPLY x.nodes('Systems/System') AS X(a);

  As I don't know the full schema of the XML, I wasn't sure if any of the nodes in the xpath repeat, but this should at least help get you on track.

• Michael F-386896

  SSC Enthusiast

  Points: 155

  More actions

  December 3, 2012 at 5:37 am

  #1564811

  Hi SSComitted,

  ... this worked like a charm - you really helped me out a lot here,

  thanks again and have a good time !

  greetings from Munich, Michael F.

• Arthur Olcot

  SSCertifiable

  Points: 6759

  More actions

  December 3, 2012 at 5:43 am

  #1564813

  No problem....thanks for the feedback

• kly411

  SSC Rookie

  Points: 49

  More actions

  June 9, 2013 at 7:33 am

  #1622270

  +1 - BIG HELP. Thanks.