1

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)
Improve this question
1
  • Can you try with WITH XMLNAMESPACES? That way you can put more than 1 Commented Jan 10, 2018 at 23:07

2 Answers 2

Reset to default
4

Yep. WITH XMLNAMESPACES. EG

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>';

with xmlnamespaces ('http://schema.somedomain.com/eei/asset/v1' as abc,
                    'http://schema.somedomain.com/eei/report/v1' as eeir)
Select m.c.query('.')
        ,m.c.value('@eeir:section', 'nvarchar(255)') section
        ,m.c.value('@filename', 'nvarchar(255)') filename
from @var.nodes('/abc:Asset/abc:Photos/abc:Photo') as m(c)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

upvote from my side, but a tiny hint: At least for me it's better to use DEFAULT with the pure namespace. No need for the abc:...
1

I'd use this:

WITH XMLNAMESPACES (DEFAULT 'http://schema.somedomain.com/eei/asset/v1'
                           ,'http://schema.somedomain.com/eei/report/v1' as eeir)
SELECT B.Section
      ,ph.value('@filename','nvarchar(max)') AS [Filename]
      ,ph.value('@eeir:webVisible','bit') AS WebVisible
      ,@var.value('(/Asset/eeir:Sections/eeir:Section[@id=sql:column("B.Section")]/@label_en)[1]','nvarchar(max)') AS Label_en
FROM @var.nodes('/Asset/Photos/Photo') AS A(ph)
outer apply(SELECT ph.value('@eeir:section', 'nvarchar(255)') AS Section) AS B;

Defining the pure namespace as DEFAULT (the xmlns without a postfix - the later ns-prefix), allows to query the XML without the need to prefix each and any name.

Reading the photo's section within an APPLY allows to create a real column, which can be used with sql:column to bind the corresponding section (similar to a JOIN) and include its data into the resultset - if needed.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.