CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
ke3pup
ke3pup

Reputation: 1895

Striping All values form XML string using TSQL (SQL SERVER 2008)

I'm looking into a problem where i need to be comparing a given XML to what user inputs without the values, so for example if this is the user input:

    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
        <soapenv:Body>
            <genRetrieve xmlns:v1="http://xxxxxxxxxxxxxxxxxxxxx">
                <checkRetrieve>
                    <party>
                        <user>
                            <first>BLA</first>
                            <last>last</last>
                        </user>
                        <media>none</media>
                    </party>
                </checkRetrieve>
            </genRetrieve>
        </soapenv:Body>
    </soapenv:Envelope>

I need to produce the following and insert it into another table:

  <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
        <soapenv:Body>
            <genRetrieve xmlns:v1="http://xxxxxxxxxxxxxxxxxxxxx">
                <checkRetrieve>
                    <party>
                        <user>
                            <first></first>
                            <last></last>
                        </user>
                        <media></media>
                    </party>
                </checkRetrieve>
            </genRetrieve>
        </soapenv:Body>
    </soapenv:Envelope>

without the formatting (spaces, line feeds ...etc).

Can anyone please give an example on how this could be archived? I intially used the blow metoh:

    DECLARE @hdoc     int
    DECLARE @doc      varchar(2000)

    SET @doc = 'my xml'

    EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

    SELECT * FROM OPENXML( @hdoc, '/*',2)

    EXEC sp_xml_removedocument @hdoc
    GO

but it seemes tricky to build the final XML string using the output of the table from above command, if you could show me the standard/most efficient way (given there will xml strings with many elements in them) it would be much appreciated.

Upvotes: 1

Views: 110

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can use the XML data type and use delete (XML DML) to remove all text() nodes.

declare @xml xml = 
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  <soapenv:Body>
    <genRetrieve xmlns:v1="http://xxxxxxxxxxxxxxxxxxxxx">
      <checkRetrieve>
        <party>
          <user>
            <first>BLA</first>
            <last>last</last>
          </user>
          <media>none</media>
        </party>
      </checkRetrieve>
    </genRetrieve>
  </soapenv:Body>
</soapenv:Envelope>'

set @xml.modify('delete //*/text()')

select @xml

Result:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  <soapenv:Body>
    <genRetrieve xmlns:v1="http://xxxxxxxxxxxxxxxxxxxxx">
      <checkRetrieve>
        <party>
          <user>
            <first />
            <last />
          </user>
          <media />
        </party>
      </checkRetrieve>
    </genRetrieve>
  </soapenv:Body>
</soapenv:Envelope>

Upvotes: 2

Related Questions