Home SQL: How do I extract attribute value from XML with multiple namespaces
Reply: 0

SQL: How do I extract attribute value from XML with multiple namespaces

user8173
1#
user8173 Published in July 21, 2018, 9:47 am

I have xml data stored in varchar column in SQL Server 2012 database. I am trying to extract the attribute filenames (guid) of photos. My query below is able to extract that. But I want to now extract the sections to which the photo belongs to and am having trouble figuring out how to extract since it is in a different schema. Any help would be appreciated.

sql fiddle demo

Declare 
@var xml = '<Asset xmlns="http://schema.somedomain.com/eei/asset/v1" xmlns:ui="http://schema.somedomain.com/taxonomy/asset-group-specific/ui/v1" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:eeir="http://schema.somedomain.com/eei/report/v1" xmlns:eeia="http://schema.somedomain.com/eei/asset/v1" categoryCode="PLM" inventoryId="A7C135B1-5272-46F9-9967-B3946851D987">
  <eeir:Sections>
    <eeir:Section id="General" label_en="General Description" complete="1" />
    <eeir:Section id="Operator Station" label_en="Operator Station" complete="1" />
    <eeir:Section id="Engine" label_en="Engine" complete="1" />
    <eeir:Section id="Frt Att" label_en="Front Attachment" complete="1" />
    <eeir:Section id="Rear Att" label_en="Rear Attachment" complete="1" />
    <eeir:Section id="Body Details" label_en="Body Details" complete="1" />
    <eeir:Section id="Drive Train" label_en="Drive Train" complete="1" />
    <eeir:Section id="U/C" label_en="Undercarriage" complete="1" />
    <eeir:Section id="Accessories" label_en="Accessories" complete="1" />
    <eeir:Section id="Functions" label_en="Functionality Tests" complete="1" />
    <eeir:Section id="Dimensions" label_en="Dimensions" complete="1" />    
  </eeir:Sections>
  <Photos>
    <Photo eeir:section="General" filename="d13ac4e5-9898-42f1-8472-33c68ed9b680.jpg" eeir:webVisible="1" />
    <Photo eeir:section="General" filename="20c2bab5-7f3f-4897-a453-b8dfa0bad7ea.jpg" eeir:webVisible="1" />
    <Photo eeir:section="General" filename="73549c1f-7777-4c60-86bb-1ab065a6ee67.jpg" eeir:webVisible="1" />
    <Photo eeir:section="General" filename="7fa69bfc-7e34-48ec-b177-e2b44d87d861.jpg" eeir:webVisible="1" />
    <Photo eeir:section="General" filename="91253949-7e34-4f09-9bed-8ef8fa081768.jpg" eeir:webVisible="1" />
    <Photo eeir:section="General" filename="7d6b50c9-7e34-4249-92bb-b8d09aa0f678.jpg" eeir:webVisible="1" />
    <Photo eeir:section="General" filename="ecf52ad4-7e34-4db1-8ca1-0c6e4092777b.jpg" eeir:webVisible="1" eeir:odometer="1" />
    <Photo eeir:section="Operator Station" filename="40af21b0-ce42-4127-074a-f05450ce5400.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Operator Station" filename="6c1cd20f-c186-4fa8-8f61-20effacbc7db.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Operator Station" filename="66bad365-0a0b-4ec0-b4d5-262d00a610e4.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Operator Station" filename="501f614a-8d00-4528-b576-80020b0b0006.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Operator Station" filename="6e7c7baf-2302-4488-aa12-be8652bd1b00.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Operator Station" filename="f0103371-84eb-40ae-8743-7071a623f1e0.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Operator Station" filename="be583343-cb88-4ca2-8de4-208db4ed2043.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Operator Station" filename="b31b2806-7525-4d48-b8e2-7e04cf07bbc4.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Operator Station" filename="7bdf831e-3105-4d88-06af-bfe0b116aff2.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Engine" filename="a0b01b04-5ce5-40ae-b5bc-d90c0390d179.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Engine" filename="dff45a13-4439-4a37-a06e-10a044b12512.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Engine" filename="99127026-75df-450e-a274-dc05740fff6b.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Engine" filename="c971aeb1-a74a-470b-0b60-65dfb1670bb5.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Engine" filename="356f1e9c-fee3-4004-a6c7-e91f24e24735.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Engine" filename="ed042e95-07d6-4092-b259-1ab0ff063d3a.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Engine" filename="023e5494-a4c1-4e1a-002f-4a3236057a20.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Engine" filename="26949e00-a4aa-4be4-9394-afa3a7b90c90.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="222e7079-cd5a-2921-a1db-ec670d6d3cc2.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="a1a2e0b2-9cce-27d2-829c-c992c1f10c0d.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="72c671c2-2dad-2b55-8697-817cb2111f1f.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="969730fa-bf12-2aa0-a960-a7a02e6b022f.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="82850913-20d1-20d6-9227-8bb2e238ec2e.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="c7b2ab98-9b19-23a5-9d2a-00b23288bd06.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="6e1ab066-067a-2cd1-bfca-30a88622e9b5.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="c37da7ea-2737-2663-836f-c392fcd8b0a8.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="36cf65f3-0ade-2c68-8d56-a2d65537712f.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="775be087-f1c8-21b2-a2c5-9de3131b67b2.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="077f2fdd-23c5-2c6b-b22f-3ffb2bba5351.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="1113f735-9de9-2a2c-af03-33d535975038.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="cd9ed222-339b-2ba8-99a9-02eef26f99fd.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="2736c10e-a021-2332-bfd8-6bbb126a6e3b.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Frt Att" filename="b50a3838-950b-2228-9a22-c21f022173d6.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Body Details" filename="0190c5d9-9d2d-9eda-91a5-52b3c1d6268d.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Body Details" filename="c9e838eb-559a-96eb-8226-f887eb02b7e8.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Drive Train" filename="1f5923bc-91b8-93ef-a608-993a82f90e9f.jpg" eeir:webVisible="1" />
    <Photo eeir:section="U/C" filename="f9d666bc-39d8-9acf-9520-399a1776d360.jpg" eeir:webVisible="1" />
    <Photo eeir:section="U/C" filename="5dc191fb-df88-97d3-9268-8c938cd309f8.jpg" eeir:webVisible="1" />
    <Photo eeir:section="U/C" filename="89753be1-582c-96e7-aafc-998bbcb3af9b.jpg" eeir:webVisible="1" />
    <Photo eeir:section="U/C" filename="6079bb2e-aeea-970b-b3b7-1893189eb978.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Accessories" filename="e97275bf-e97d-9c15-b292-da710f9852ee.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Accessories" filename="092f65ec-5199-9c2e-b8f0-a9d5ff935716.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Accessories" filename="2525909a-fb5b-953b-bd22-d139a9e6cc1f.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Accessories" filename="d8cee01b-99d0-9997-9cea-5c7a555562a0.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Accessories" filename="c223792a-8053-99a1-af39-f63bb99ef932.jpg" eeir:webVisible="1" />
    <Photo eeir:section="Accessories" filename="9e0cd913-7032-98cf-a65e-07c19571991d.jpg" eeir:webVisible="1" />
  </Photos>    
</Asset>'
Select m.c.query('.')
        ,m.c.value('@section', 'nvarchar(255)')
        ,m.c.value('@filename', 'nvarchar(255)')
from @var.nodes('declare namespace abc="http://schema.somedomain.com/eei/asset/v1"; /abc:Asset/abc:Photos/abc:Photo') as m(c)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.316119 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO