0

I have a xml docment structure which looks like the following 

<X>
    <Y>Noah</Y>
    <Y>111 Fake St</Y>
    <Y>888-555-5555</Y>
</X>
<X>
    <Y>Jonh</Y>
    <Y>123 Jump St</Y>
    <Y>888-500-5000</Y>
</X>
<X>
   .
   .
   .
</X>

The above is an example of one row of data in the table column code_xml

I was having trouble figuring out how to break the X nodes into separate rows and making Y nodes into their appropriate columns like the following:

enter image description here

I was reviewing the XML documentation and trying to use the different Sql Server XML Methods such as nodes and value but was running into a wall.

I felt as though using a cross apply using the nodes function to break up each inner X element, then using the value method on the Y elements with their appropriate positional value would do the trick.

I was failing because I was not using the proper dot notation as seen below:

select 
    d.p.value('./Y[1]', 'varchar(200)') Name,
    p.value('./Y[2]', 'varchar(200)') Address,
    p.value('./Y[3]', 'varchar(200)') Phone
from
    T
cross apply 
    T.Code_xml.nodes('/X') as d(p)

Then I found this Stack Overflow post and helped me solve my issue.

Noah Joseph Anderson
  • 303
  • 1
  • 2
  • 9
  • Does this answer your question? [Convert Xml to Table SQL Server](https://stackoverflow.com/q/3989395/2029983). You link to a post that helped solve the issue, if this is simply a duplicate of that (which it seems like it is) you should upvote the other answer, not duplicate. – Thom A Jan 28 '20 at 09:12
  • 1
    It's not an blog site, no, @marc_s , however, posting and answering your own questions is allowed, and is in fact encouraged: [It’s OK to Ask and Answer Your Own Questions](https://stackoverflow.blog/2011/07/01/its-ok-to-ask-and-answer-your-own-questions/) – Thom A Jan 28 '20 at 09:15
  • Thanks @Larnu for reminding me to up vote the other answer as I do want to freely do so. Honestly I had to rewrite this question several times and then when I found the answer, I figured let me post it, post my answer, then hopefully if this answer which I found is not as clear or mine is not then maybe one of our posts will help solidify the understanding. Thanks, but I do not see that it is a duplicate, just a similar answer to a problem. My question has more than just getting rows but also getting nodes at the same level with the same same Name ie (Y[1],Y[2],Y[3] – Noah Joseph Anderson Jan 30 '20 at 03:35

1 Answers1

0

Below is a good working example of how to solve converting multiple nodes in one row into multiple sql rows and then getting positional nodes values from elements with the same name.

and wrote the following code example:

declare @xml nvarchar(max) = '<X>
    <Y>Noah</Y>
    <Y>111 Fake St</Y>
    <Y>888-555-5555</Y>
</X>
<X>
    <Y>Jonh</Y>
    <Y>123 Jump St</Y>
    <Y>888-500-5000</Y>
</X>'

select d.p.value('./Y[1]', 'varchar(200)') Name
,d.p.value('./Y[2]', 'varchar(200)') Address
,d.p.value('./Y[3]', 'varchar(200)') Phone
from (select CAST(@xml as XML) as Code_xml) T
cross apply T.Code_xml.nodes('/X') as d(p)

I am getting what appears to be positive results.

Noah Joseph Anderson
  • 303
  • 1
  • 2
  • 9