How to convert XML data into a SQL Server table

Question

<OrderContact>
    <Contact>
        <ContactName>Harj Dhamrait</ContactName>
        <ContactDescription>13</ContactDescription>
        <ListOfContactNumber>
            <ContactNumber>
                <ContactNumberValue />454854 5532281</ContactNumberValue>
                <ContactNumberTypeCoded>TelephoneNumber</ContactNumberTypeCoded>
            </ContactNumber>
            <ContactNumber>
                <ContactNumberValue>0987262 532281</ContactNumberValue>
                <ContactNumberTypeCoded>Other</ContactNumberTypeCoded>
                <ContactNumberTypeCodedOther>Switchboard</ContactNumberTypeCodedOther>
            </ContactNumber>
            <ContactNumber>
                <ContactNumberValue>[email protected]</ContactNumberValue>
                <ContactNumberTypeCoded>EmailAddress</ContactNumberTypeCoded>
            </ContactNumber>
            <ContactNumber>
                <ContactNumberValue>01322 296 252</ContactNumberValue>
                <ContactNumberTypeCoded>FaxNumber</ContactNumberTypeCoded>
            </ContactNumber>
        </ListOfContactNumber>
    </Contact>
</OrderContact>

I need to convert this to a SQL Server table:

TelephoneNumber	Switchboard	EmailAddress	FaxNumber
454854 5532281	0987262 532281	[email protected]	01322 296 252

marc_s · Accepted Answer · 2021-03-09 07:58:47Z

Try this:

DECLARE @Data XML = '<OrderContact>
    <Contact>
        <ContactName>Harj Dhamrait</ContactName>
        <ContactDescription>13</ContactDescription>
        <ListOfContactNumber>
            <ContactNumber>
                <ContactNumberValue>454854 5532281</ContactNumberValue>
                <ContactNumberTypeCoded>TelephoneNumber</ContactNumberTypeCoded>
            </ContactNumber>
            <ContactNumber>
                <ContactNumberValue>0987262 532281</ContactNumberValue>
                <ContactNumberTypeCoded>Other</ContactNumberTypeCoded>
                <ContactNumberTypeCodedOther>Switchboard</ContactNumberTypeCodedOther>
            </ContactNumber>
            <ContactNumber>
                <ContactNumberValue>[email protected]</ContactNumberValue>
                <ContactNumberTypeCoded>EmailAddress</ContactNumberTypeCoded>
            </ContactNumber>
            <ContactNumber>
                <ContactNumberValue>01322 296 252</ContactNumberValue>
                <ContactNumberTypeCoded>FaxNumber</ContactNumberTypeCoded>
            </ContactNumber>
        </ListOfContactNumber>
    </Contact>
</OrderContact>'

SELECT
    TelephoneNumber = xc.value('(ContactNumber[ContactNumberTypeCoded="TelephoneNumber"]/ContactNumberValue/text())[1]', 'varchar(50)'),
    Switchboard = xc.value('(ContactNumber[ContactNumberTypeCodedOther="Switchboard"]/ContactNumberValue/text())[1]', 'varchar(50)'),
    EmailAddress = xc.value('(ContactNumber[ContactNumberTypeCoded="EmailAddress"]/ContactNumberValue/text())[1]', 'varchar(50)'),
    FaxNumber = xc.value('(ContactNumber[ContactNumberTypeCoded="FaxNumber"]/ContactNumberValue/text())[1]', 'varchar(50)')
FROM
    @Data.nodes('/OrderContact/Contact/ListOfContactNumber') AS XT(XC)

You should get the desired output:

The .nodes() method call returns a XML fragment representing the <ListOfContactNumber> node. You need to reach into that XML fragment, and extract each <ContactNumber> child node - based on what value they have in ContactNumberTypeCoded - and then show the <ContactNumberValue> value as the desired output.

Collectives™ on Stack Overflow

How to convert XML data into a SQL Server table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related