My XML procedure is so slo-o-o-o-w

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 6, 2010 at 3:02 pm

  #1189350

  Why not simply using the "old-fashioned" method?

  SELECT

  xmlFileName,

  c.value('SSN[1]', 'varchar(30)') AS ssn,

  c.value('TaxableWages[1]' , 'varchar(30)') AS TaxableWages

  FROM XmlImportTest

  CROSS APPLY

  xml_data.nodes('ROOT/Employee') T(c)

  If this solution doesn't give you the desired result you might want to post your expected data so we have something to compare against...

  ---

  
  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]

• SwePeso

  SSC-Dedicated

  Points: 39758

  More actions

  July 7, 2010 at 4:35 am

  #1189513

  DECLARE@Data XML = '

  <ROOT>

  <Employee>

  <SSN>123456789</SSN>

  <Employee>

  <FirstName>DOREEN </FirstName>

  <MiddleName>B</MiddleName>

  <LastName>jukyuk </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>10244.28</TotalWages>

  <TaxableWages>0.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>234567891</SSN>

  <Employee>

  <FirstName>ANDRE </FirstName>

  <MiddleName>R</MiddleName>

  <LastName>uykyujyj </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>20377.49</TotalWages>

  <TaxableWages>1000.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>345678912</SSN>

  <Employee>

  <FirstName>Michael </FirstName>

  <MiddleName>J</MiddleName>

  <LastName>srdgretr5e </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>549.00</TotalWages>

  <TaxableWages>549.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>456789123</SSN>

  <Employee>

  <FirstName>MATTHEW </FirstName>

  <MiddleName>J</MiddleName>

  <LastName>ertesrgfes </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>1760.00</TotalWages>

  <TaxableWages>1760.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>567891234</SSN>

  <Employee>

  <FirstName>DEBORAH </FirstName>

  <MiddleName>L</MiddleName>

  <LastName>sertesrtesr </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>5170.31</TotalWages>

  <TaxableWages>5170.31</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>678912345</SSN>

  <Employee>

  <FirstName>COREY </FirstName>

  <MiddleName>R</MiddleName>

  <LastName>srtertsettt </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>238.00</TotalWages>

  <TaxableWages>238.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>789123456</SSN>

  <Employee>

  <FirstName>JADE </FirstName>

  <LastName>ertestert </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>1509.82</TotalWages>

  <TaxableWages>1509.82</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>891234567</SSN>

  <Employee>

  <FirstName>MICHAEL </FirstName>

  <MiddleName>L</MiddleName>

  <LastName>ertertert </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>6232.92</TotalWages>

  <TaxableWages>0.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>912345678</SSN>

  <Employee>

  <FirstName>DOREEN </FirstName>

  <MiddleName>B</MiddleName>

  <LastName>erstserrrrrrr </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>10244.28</TotalWages>

  <TaxableWages>0.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>112345678</SSN>

  <Employee>

  <FirstName>ANDRE </FirstName>

  <MiddleName>R</MiddleName>

  <LastName>rdtrettttttttt </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>20377.49</TotalWages>

  <TaxableWages>1000.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>223456789</SSN>

  <Employee>

  <FirstName>Michael </FirstName>

  <MiddleName>J</MiddleName>

  <LastName>rdgrsetert </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>549.00</TotalWages>

  <TaxableWages>549.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>334567891</SSN>

  <Employee>

  <FirstName>MATTHEW </FirstName>

  <MiddleName>J</MiddleName>

  <LastName>ergsertete </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>1760.00</TotalWages>

  <TaxableWages>1760.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>445678912</SSN>

  <Employee>

  <FirstName>DEBORAH </FirstName>

  <MiddleName>L</MiddleName>

  <LastName>fgrgre </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>5170.31</TotalWages>

  <TaxableWages>5170.31</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>556789123</SSN>

  <Employee>

  <FirstName>COREY </FirstName>

  <MiddleName>R</MiddleName>

  <LastName>fbsrggser </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>238.00</TotalWages>

  <TaxableWages>238.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>667891234</SSN>

  <Employee>

  <FirstName>JADE </FirstName>

  <LastName>erterg </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>1509.82</TotalWages>

  <TaxableWages>1509.82</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  <Employee>

  <SSN>778912345</SSN>

  <Employee>

  <FirstName>MICHAEL </FirstName>

  <MiddleName>L</MiddleName>

  <LastName>dfhtrhh </LastName>

  <Suffix/>

  </Employee>

  <TotalWages>6232.92</TotalWages>

  <TaxableWages>0.00</TaxableWages>

  <OutOfStateWages>

  <TotalWages> 0.00</TotalWages>

  <TaxableWages> 0.00</TaxableWages>

  <TaxState>XX</TaxState>

  </OutOfStateWages>

  </Employee>

  </ROOT>'

  SELECTn.value('../../SSN[1]', 'VARCHAR(MAX)') AS SSN,

  n.value('../../TotalWages[1]', 'MONEY') AS TotalWages,

  n.value('../../TaxableWages[1]', 'MONEY') AS TaxableWages,

  n.value('local-name(..)', 'VARCHAR(MAX)') AS ParentName,

  n.value('local-name(.)', 'VARCHAR(MAX)') AS ElementName,

  n.value('.', 'VARCHAR(MAX)') AS ElementValue

  FROM@Data.nodes('/*/*/*/*') AS r(n)

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• rightfield

  SSCrazy

  Points: 2418

  More actions

  July 7, 2010 at 6:19 am

  #1189555

  Thank you both. I have never done anything with XML in TSQL. With these working solutions I will be able to learn a bit more. Thank you.