March 18, 2019 at 12:23 pm
I am looking to have the xml column converted into a table. Basically shred the xml into columns. I am not familiar with XML and tried a tad bit of stuff but couldn't make any progress. Any help would be appreciated? Below I have provided a sample xml code that is part of the table.
create table x (abc xml null, id int not null) on [primary] textimage_on [primary];
select abc from x -- It will hold data as below I have just provided one row as an example. However, it has 100s of rows
<RelatedParties xmlns: i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/IntApp.WilCo.Model.Forms.AnswerTypes">
<RelatedParty>
<Modified>false</Modified>
<Name>Lewis</Name>
<Notes />
<Position>Adversary</Position>
<QuestionId i:nil='"true" />
<Relationship />
<Searche>true</Searched>
</RelatedParty>
<RelatedParty>
<Modified>false</Modified>
<Name>Greg</Name>
<Notes />
<Position>Adversary</Position>
<QuestionId i:nil='"true" />
<Relationship />
<Searche>true</Searched>
</RelatedParty>
</RelatedParties>
March 18, 2019 at 1:35 pm
Feivel - Monday, March 18, 2019 12:23 PMI am looking to have the xml column converted into a table. Basically shred the xml into columns. I am not familiar with XML and tried a tad bit of stuff but couldn't make any progress. Any help would be appreciated? Below I have provided a sample xml code that is part of the table.
create table x (abc xml null, id int not null) on [primary] textimage_on [primary];select abc from x -- It will hold data as below I have just provided one row as an example. However, it has 100s of rows
<RelatedParties xmlns: i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/IntApp.WilCo.Model.Forms.AnswerTypes">
<RelatedParty>
<Modified>false</Modified>
<Name>Lewis</Name>
<Notes />
<Position>Adversary</Position>
<QuestionId i:nil='"true" />
<Relationship />
<Searche>true</Searched>
</RelatedParty>
<RelatedParty>
<Modified>false</Modified>
<Name>Greg</Name>
<Notes />
<Position>Adversary</Position>
<QuestionId i:nil='"true" />
<Relationship />
<Searche>true</Searched>
</RelatedParty>
</RelatedParties>
First, your XML appears to be poorly formed
Your namespace has an additional space in the definition
<RelatedParties xmlns: i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/IntApp.WilCo.Model.Forms.AnswerTypes">
In this sample, the opening and closing tags do not match
<Searche>true</Searched>
That said, I believe that the following SQL will get what you are looking for. You may need to adjust the data types as needed.WITH XMLNAMESPACES(
DEFAULT 'http://schemas.datacontract.org/2004/07/IntApp.WilCo.Model.Forms.AnswerTypes'
)
SELECT
x.id
, Modified = Related.Party.value('(Modified/text())[1]', 'bit')
, Name = Related.Party.value('(Name/text())[1]', 'varchar(100)')
, Notes = Related.Party.value('(Notes/text())[1]', 'varchar(255)')
, Position = Related.Party.value('(Position/text())[1]', 'varchar(50)')
, QuestionId = Related.Party.value('(QuestionId/text())[1]', 'int')
, Relationship = Related.Party.value('(Relationship/text())[1]', 'varchar(100)')
, Searched = Related.Party.value('(Searched/text())[1]', 'bit')
FROM x
CROSS APPLY abc.nodes('RelatedParties/RelatedParty') AS Related(Party);
March 19, 2019 at 10:22 am
DesNorton - Monday, March 18, 2019 1:35 PMFeivel - Monday, March 18, 2019 12:23 PMI am looking to have the xml column converted into a table. Basically shred the xml into columns. I am not familiar with XML and tried a tad bit of stuff but couldn't make any progress. Any help would be appreciated? Below I have provided a sample xml code that is part of the table.
create table x (abc xml null, id int not null) on [primary] textimage_on [primary];select abc from x -- It will hold data as below I have just provided one row as an example. However, it has 100s of rows
<RelatedParties xmlns: i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/IntApp.WilCo.Model.Forms.AnswerTypes">
<RelatedParty>
<Modified>false</Modified>
<Name>Lewis</Name>
<Notes />
<Position>Adversary</Position>
<QuestionId i:nil='"true" />
<Relationship />
<Searche>true</Searched>
</RelatedParty>
<RelatedParty>
<Modified>false</Modified>
<Name>Greg</Name>
<Notes />
<Position>Adversary</Position>
<QuestionId i:nil='"true" />
<Relationship />
<Searche>true</Searched>
</RelatedParty>
</RelatedParties>First, your XML appears to be poorly formed
Your namespace has an additional space in the definition
<RelatedParties xmlns: i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/IntApp.WilCo.Model.Forms.AnswerTypes">
In this sample, the opening and closing tags do not match<Searche>true</Searched>
That said, I believe that the following SQL will get what you are looking for. You may need to adjust the data types as needed.
WITH XMLNAMESPACES(
DEFAULT 'http://schemas.datacontract.org/2004/07/IntApp.WilCo.Model.Forms.AnswerTypes'
)
SELECT
x.id
, Modified = Related.Party.value('(Modified/text())[1]', 'bit')
, Name = Related.Party.value('(Name/text())[1]', 'varchar(100)')
, Notes = Related.Party.value('(Notes/text())[1]', 'varchar(255)')
, Position = Related.Party.value('(Position/text())[1]', 'varchar(50)')
, QuestionId = Related.Party.value('(QuestionId/text())[1]', 'int')
, Relationship = Related.Party.value('(Relationship/text())[1]', 'varchar(100)')
, Searched = Related.Party.value('(Searched/text())[1]', 'bit')
FROM x
CROSS APPLY abc.nodes('RelatedParties/RelatedParty') AS Related(Party);
Everything came out as I expected it to be but only the issue is with QuestionId...it is returning NULL or empty. Can we fix this? Thank you so much for your help.
March 19, 2019 at 12:02 pm
Feivel - Tuesday, March 19, 2019 10:22 AMEverything came out as I expected it to be but only the issue is with QuestionId...it is returning NULL or empty. Can we fix this? Thank you so much for your help.
The sample you provided had QuestionId as NULL in both instances. If you provide data where QuestionId is not null, then we can try to modify the SQL accordingly.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply